<\/span><\/h3>\n\n\n\nIn this mode, SQL Server trusts Windows to verify your identity. You log into SQL Server using your Windows credentials\u2014no need for a separate SQL username and password. It\u2019s super secure because it uses your domain\u2019s policies like password expiration, complexity, etc.<\/p>\n\n\n\n
Pro:<\/strong><\/p>\n\n\n\n\n- Seamless, secure, no extra password needed.<\/li>\n<\/ul>\n\n\n\n
Con:<\/strong><\/p>\n\n\n\n\n- No access for SQL-specific logins like SA unless explicitly enabled.<\/li>\n<\/ul>\n\n\n\n
<\/span>Mixed Mode Authentication (SQL Server and Windows)<\/strong><\/span><\/h3>\n\n\n\nMixed Mode lets SQL Server accept two types of logins:<\/p>\n\n\n\n
\n- Windows accounts<\/li>\n\n\n\n
- SQL Server accounts (like SA)<\/li>\n<\/ul>\n\n\n\n
Pro:<\/strong><\/p>\n\n\n\n\n- Flexibility to create separate SQL logins.<\/li>\n\n\n\n
- Required for many third-party apps or integrations.<\/li>\n<\/ul>\n\n\n\n
Con:<\/strong><\/p>\n\n\n\n\n- If poorly managed, SQL logins (especially SA) can become a security risk.<\/li>\n<\/ul>\n\n\n\n
Why does this matter?<\/strong>
Because the SA account only works if Mixed Mode is enabled<\/strong>! If your server is set to Windows-only authentication, you can try logging in as SA until you\u2019re blue in the face\u2014it won\u2019t work.<\/p>\n\n\n\nKnowing which mode your SQL Server is operating in is your first checkpoint before enabling SA.<\/p>\n\n\n\n
<\/span>Checking the Current Authentication Mode<\/strong><\/span><\/h2>\n\n\n\nAlright, time to peek under the hood and check what authentication mode your SQL Server is using. Here\u2019s how you do it:<\/p>\n\n\n\n
<\/span>Method 1: Using SQL Server Management Studio (SSMS)<\/strong><\/span><\/h3>\n\n\n\n\n- Open SQL Server Management Studio<\/strong> (SSMS).<\/li>\n\n\n\n
- Connect to your SQL Server instance using Windows Authentication.<\/li>\n\n\n\n
- Right-click on the server name in Object Explorer<\/strong> and choose Properties<\/strong>.<\/li>\n\n\n\n
- In the left-hand menu, click Security<\/strong>.<\/li>\n\n\n\n
- Look at the Server authentication<\/strong> section:\n
\n- If it says Windows Authentication mode<\/strong>, you\u2019ll need to switch it.<\/li>\n\n\n\n
- If it says SQL Server and Windows Authentication mode<\/strong>, you\u2019re good to go!<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n
Simple, right?<\/p>\n\n\n\n
<\/span>Method 2: Using SQL Query<\/strong><\/span><\/h3>\n\n\n\nIf you prefer flexing some T-SQL muscles, run this command:<\/p>\n\n\n\n
sq
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS [Authentication Mode];
<\/pre>\n\n\n\n
\n- If it returns
1<\/code>, you’re in Windows Authentication mode.<\/li>\n\n\n\n- If it returns
0<\/code>, you’re in Mixed Mode.<\/li>\n<\/ul>\n\n\n\nQuick Tip:<\/strong>
If you’re managing servers remotely or automating deployments, checking via SQL query saves a ton of time!<\/p>\n\n\n\n<\/span>Enabling Mixed Mode Authentication<\/strong><\/span><\/h2>\n\n\n\nIf your server is stuck in Windows Authentication mode, don’t worry\u2014it\u2019s an easy fix. Here’s how to switch to Mixed Mode step-by-step.<\/p>\n\n\n\n
<\/span>Enable Mixed Mode via SSMS:<\/strong><\/span><\/h3>\n\n\n\n\n- Open SSMS<\/strong> and connect using Windows Authentication.<\/li>\n\n\n\n
- Right-click the server name in Object Explorer<\/strong>, click Properties<\/strong>.<\/li>\n\n\n\n
- Navigate to the Security<\/strong> page.<\/li>\n\n\n\n
- Select SQL Server and Windows Authentication mode<\/strong>.<\/li>\n\n\n\n
- Click OK<\/strong> to save the settings.<\/li>\n<\/ol>\n\n\n\n
But wait, you\u2019re not done yet!<\/strong><\/p>\n\n\n\nImportant:<\/strong>
You must restart<\/strong> the SQL Server service for changes to take effect.<\/p>\n\n\n\n<\/span>Restart SQL Server Service:<\/strong><\/span><\/h3>\n\n\n\n\n- Open SQL Server Configuration Manager<\/strong>.<\/li>\n\n\n\n
- Locate your instance under SQL Server Services<\/strong>.<\/li>\n\n\n\n
- Right-click the service name, and click Restart<\/strong>.<\/li>\n<\/ul>\n\n\n\n
Boom\u2014you just switched to Mixed Mode! Now SQL logins like SA can work again.<\/p>\n\n\n\n
<\/span>Enabling the SA Account<\/strong> in SQL Server<\/strong><\/span><\/h2>\n\n\n\nNow comes the part you\u2019ve been waiting for\u2014bringing the SA account back to life<\/strong>.<\/p>\n\n\n\n<\/span>Steps to Enable SA Account<\/strong> in SQL Server<\/strong>:<\/strong><\/span><\/h3>\n\n\n\n\n- In SSMS, expand Security<\/strong> > Logins<\/strong>.<\/li>\n\n\n\n
- Right-click on the sa<\/strong> login and select Properties<\/strong>.<\/li>\n\n\n\n
- Under General<\/strong>, set a strong password<\/strong>.<\/li>\n\n\n\n
- Switch to the Status<\/strong> tab.<\/li>\n\n\n\n
- Set:\n
\n- Login:<\/strong> Enabled<\/li>\n\n\n\n
- Permission to connect to database engine:<\/strong> Grant<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- Click OK<\/strong>.<\/li>\n<\/ol>\n\n\n\n
Congratulations\u2014the SA account is now active!<\/p>\n\n\n\n
Important:<\/strong>
Set a password that\u2019s not just “password123” or “admin”. Treat this account like Fort Knox. Use a combination of uppercase, lowercase, numbers, and special characters.<\/p>\n\n\n\n