SQL Server Authentication Modes: Windows vs. Mixed Mode Security

When configuring SQL Server, one of the first decisions you’ll face is choosing the authentication mode. SQL Server offers two primary modes of authentication: Windows Authentication Mode and Mixed Mode Authentication. In general you are going to want to leave that in Windows Authentication Mode unless you have a compelling reason not to. In reality, most shared SQL instance will need Mixed Mode at some point.

---

Windows Authentication Mode

Windows Authentication is the default and recommended option for securing SQL Server. It leverages Active Directory (AD) to authenticate users, allowing for centralized management of credentials and permissions.

Advantages of Windows Authentication:

1. Centralized Management: Administrators can manage user accounts, passwords, and permissions directly through AD.
2. Kerberos Support: Windows Authentication uses the Kerberos protocol for secure authentication, providing enhanced security and performance.
3. Password Management: SQL Server doesn’t store or manage passwords in this mode, reducing the risk of credential theft. An often forgotten benefit of this is the ease of migrating logins and databases between instances. There is no chance of a mismatched SID when using Windows Authentication
4. Group Management: The ability to have a group and create a single login for all members of that group is often forgotten. This takes the centralized Management component in point one to another level allowing you to bring real role based management capabilities to your SQL environment.

Limitations of Windows Authentication:

1. Dependency on Active Directory: This mode requires a robust AD setup, making it less ideal for standalone servers or environments without AD integration.
2. Limited Flexibility: Non-Windows users or external applications may face challenges accessing the server. Scratch that – even SQL Server itself may have problems using Windows logins. For example using Windows authentication between linked servers will very quickly run you into a double-hop credential error unless you plan for it in advance.

---

Mixed Mode Authentication

Mixed Mode Authentication supports both Windows Authentication and SQL Server Authentication. In addition to using AD accounts, it allows SQL Server to manage its own user accounts, often referred to as SQL logins.

Advantages of Mixed Mode Authentication:

1. Supports Non-Windows Clients: This mode is ideal for applications or users outside the AD ecosystem.
2. Greater Flexibility: Mixed Mode provides more options for authenticating users and systems.

Limitations of Mixed Mode Authentication:

1. Password Management Burden: SQL Server accounts require manual password policies and management, increasing administrative overhead. However from about SQL 2005 this is less of an issue. When creating a login the “Enforce Password Policy” box allows you to enforce those policies, while leaving the “Enforce Password Expiry” box unchecked removes the necessity of continually updating the passwords used in connection strings.
2. Security Risks: SQL logins can be more vulnerable to brute-force attacks if strong password policies aren’t enforced. Additionally, it’s not uncommon to find that SQL logins are exposed in plain text in configuration files or connection strings.
3. Shared Credentials: People are people…and inevitably someone is going to use that SQL login they found in a connection string to login to the database directly. Or an authorised user may share that login with another user while they are “waiting for their access to be authorised”. There’s a mountain of scenarios where people will begin using a SQL login, and the truth is that it is very hard to track even if you are looking for it.

---

Key Considerations for Choosing the Right Mode

1. Environment Type:
   • For corporate environments with AD infrastructure, Windows Authentication is typically the better choice.
   • For standalone servers or systems requiring third-party integrations, Mixed Mode might be necessary.
2. Security Requirements:
   • Windows Authentication reduces the attack surface by avoiding local SQL Server accounts.
   • Mixed Mode requires careful configuration to minimize risks, such as enforcing strong password policies and auditing login attempts.
3. Application Needs:
   • Mixed mode authentication often ends up becoming a practical necessity if your applications or services don’t support Windows Authentication.

---

Securing Your Chosen Mode

Regardless of the chosen mode, here are a number of best practices to follow:

• Enable Auditing: Monitor login attempts to detect and respond to suspicious activity.
• Use Encryption: Protect data in transit by enabling SSL/TLS for client connections.
• Enforce Password Policies: Implement strong, complex passwords for SQL logins in Mixed Mode. When these are stored in password vaults they should include the intended purpose and preferably the servers\ip addresses they will be used on. If you do this you can monitor for activity outside these locations to determine when the password is being shared\abused.
• Limit Privileged Accounts: Restrict sysadmin access to only those who truly need it. And revisit that group on a regular basis to ensure they still need it.

---

From time to time I find myself needing to check on the service account for one reason or another, and I can never remember the correct DMV’s and always go fishing for the details. In the spirit of putting things I keep doing on my blog so I have a place to find them, here is the correct DMV:

sys.dm_server_services

and to go a step further this is the code I use:

select
  servicename
  ,startup_type_desc
  ,service_account
from
  sys.dm_server_services

This should work on any server from SQL 2008 R2 onwards, so hopefully any server you are working on. However if you ARE working on a 19+ year old server, here’s how to do it on SQL 2005 and SQL 2008(and SQL 2008 R2 prior to SP1):

DECLARE @servaccount NVARCHAR(128);
–SQL Agent
EXEC master.dbo.xp_regread
‘HKEY_LOCAL_MACHINE’,
‘SYSTEM\CurrentControlSet\services\SQLSERVERAGENT’,
‘ObjectName’,
@servaccount OUTPUT;

SELECT @servaccount as AGENT_ServiceAccount;

–SQL Server
EXEC master.dbo.xp_regread
‘HKEY_LOCAL_MACHINE’,
‘SYSTEM\CurrentControlSet\services\MSSQLSERVER’,
‘ObjectName’,
@servaccount OUTPUT;

SELECT @servaccount as SQL_ServiceAccount;

Microsofts recommendation on TempDB on Azure VM’s is to place it on the D-drive that every Azure server is created with.  In most cases this is going to be the fastest storage on the virtual machine, and given that tempDB files do not need to be persisted this can give you access to a high performance TempDB configuration without the need to provision additional storage.

  The only issue is that the Ephemeral drive is temporary.  Everything is wiped from it whenever a machine is deallocated(Note – contents can survive a reboot, just not a shutdown-deallocation event).  Because I keep forgetting how to set it up I thought I might as well store it here so next time I forget I don’t have to log into the last VM I set up and create it.

Step 1.  Create a folder called scripts and create a file in it called SQL-Startup.ps1.  Edit that file and enter the following code.

Start-Transcript -Path "C:\Scripts\Transcript$(((get-date).ToUniversalTime()).ToString("yyyyMMdd_hhmmss")).txt" -Append
$SQLService="SQL Server (MSSQLSERVER)”
$SQLAgentService="SQL Server Agent (MSSQLSERVER)"
$tempfolder="D:\SQLTEMPDB"
if (!(test-path -path $tempfolder)) {
   New-Item -ItemType directory -Path $tempfolder
}
Start-Service $SQLService
Start-Service $SQLAgentService

$runtime= Get-Date
echo “{$runtime}: I just ran” >> C:\scripts\outputfile.txt

Step 2.  Create a Windows Scheduler Task which will run that script each time the Virtual Machine Starts.

1. Click on Windows button and type ‘Scheduler’ – select and open task scheduler.
2. Click “Create Task”
   1. On General Tab:
      1. Set name to “SQL Startup”
      2. Change user to run as a service account with permissions to create a folder and Start SQL services.
      3. Select “Run whether the user is logged in or not.
   2. On Triggers Tab:
      1. Select “New”
      2. Select “At Startup” from the dropbox menu.
      3. Ensure “Enabled” is checked and click Okay.
   3. On the Actions Tab:
      1. Select “New”
      2. Select Action as “Start a program”
      3. For the program type(or browse to) powershell.exe
      4. For Add Arguments type Powershell.exe -ExecutionPolicy Unrestricted -File “C:\Scripts\SQL-Startup.ps1”
      5. Click Okay.
   4. On the Conditions Tab:
      1. No Changes
   5. On the Settings Tab:
      1. No Changes
3. Click Okay – You will be prompted for the credentials for the service account you selected to run the job under.

Step 3.  Set SQL to auto start(delayed)

1. Click Windows Start button and type “Services.msc”
2. Right click on the SQL Service and change Startup Type to “Automatic (Delayed Start)”
3. Click Okay.

Step 4.  Ensure Service Account has correct rights

1. Ensure your service account has sufficient permissions to start SQL and create a folder.
2. Ensure your service account has “Log on as a batch job” rights.

Now you can stop SQL, delete the tempdb folder and restart the virtual machine.  On bootup the script should recreate the folder and add a record of the restart to C:\scripts\outputfile.txt

Another audit picks up “Scan for Startup Procs” and flags it as a problem. The recommendation is to turn it off. I disagree, and here’s why…

Like my previous post about xp_cmdshell, I have a stake here. We use this stored procedure in our monitoring so whenever SQL starts we can check the error logs and calculate downtime etc. It’s useful, and this is exactly the scenario that I believe the configuration is made for.

Unlike xp_cmdshell though I can see a likely configuration where this can be a problem. So let’s talk about what it does.

If you enable this option SQL will scan for stored procedures marked to run on startup next time it restarts(As a side note you need to restart the server to enable it). You can mark a stored procedure to run on startup by using sp_procoption. And when I say you, I mean YOU, the sysadmin.

Yes the only user who has the ability to mark a stored procedure to run on startup is a sysadmin, so like xp_cmdshell this is a vulnerability that can only be exploited by a sysadmin. And if they are already a sysadmin they can turn it on anyway. Also…you know…they are a sysadmin. If they are malicious you have bigger problems than them running a stored procedure on startup. They can run ANY stored procedure, right now.

Of course, it’s a bit more complex than this. In this case the stored procedure is only required to mark a stored procedure to run on startup. It is possible for a user without sysadmin privileges who has the correct rights on the master database to alter the stored procedure. So yes, there is a vector here to elevate db owner on master to sysadmin, and then do whatever else you want.

This is where I have issues with some of these generic recommendations in audits. They are painted as black and white and as we all know the answer in SQL is always ‘It depends’. But when you just label something as ‘bad’ and people just accept that rather than ask why and how, perfectly safe and useful pieces of functionality are turned off.

A better approach would be to treat this finding as the start of a conversation, sit down with the server owners, understand if the functionality is needed and if so what mitigations can be put in place to ensure it is not a risk. There’s not many good reasons for giving a user elevated rights on the master database.

I’ll stop now because this post has a lot of potential to turn into a rant on the difference between compliance and security, but if you found this blog looking for a recommendation about using “Scan for Startup Procs” my advise would be this. If there’s another way of doing what you want that is equally efficient, use that. “Scan for Startup Procs” is a setting you will find yourself having to defend every time there is an audit of your server and you may not want to fight that fight. If you do want to fight it, making the setting safe is very simple. Don’t let anyone have rights to edit the stored procedure you create and you are fine. Bare in mind however that with all your good intentions, one day you may not be there, the guidance may not be followed and someone will be able to exploit this.

Well this was an odd one, and luckily on my test environment but definitely one I’ll look out for in the future. I was doing some maintenance removing old unused test databases when suddenly I got hit with this error:

Logon failed for login ‘ROBLT\RobSQL’ due to trigger execution.
Changed database context to ‘master’.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)

This was not a fun error. Everything I tried to do in the object explorer generated this error. Basically I couldn’t create any new connections to the database, I did have an existing query window open to master database which was still working which turned out to be my saving grace, as you’ll see later.

The error is pretty clear about what is going on. There is a trigger that is failing which is preventing any new logins from being established. If this was a production server it would mean NO-ONE could connect at all, which would be raise the stakes somewhat from my play server. The thing is, as a test server I had no idea what trigger it was talking about. I vaguely remember playing with login triggers 6 months ago but have no idea which trigger I needed to stop. So from my open query window to master, I stopped them all with the below TSQL:

DISABLE TRIGGER ALL ON ALL SERVER 

This immediately allowed me to access the object explorer and go and look for the specific trigger that had caused the problem:

This trigger had of course been trying to write into one of the databases I had cleaned up. Lesson learnt – check for server scoped triggers before you hit that delete button.

There’s a number of other scenario’s that could get you into this pickle – such as creating a trigger to whitelist logins and forgetting to add yourself, or creating a trigger that references an object that doesn’t exist. If you want to be more surgical you can check for the trigger name with:

select * from sys.server_triggers

All this of course assumes you can get into the server in the first place. I was lucky enough to have an open live connection. If you don’t a failing trigger will prevent EVERYONE from connecting to the server, including sysadmins. You don’t have to hand in your resignation just yet though. Microsoft has foreseen that people will do stupid things with login triggers so there’s a few ways to address it outlined in the documentation:

1. Login using the dedicated Administrator Connection: Details of how to do that are here if you are not sure. The DAC is not affected by login triggers. If you have enabled the setting “Remote Admin Connections”(And you should) you can do this remotely, otherwise you need to be on the same server that SQL is installed on.
2. Start SQL in minimal configuration mode. You can do this by adding the -f flag when you start the server.

Either of these options will allow you to log into the server and disable or fix the trigger.