Abstract
Logins and Users are basic security concepts in SQL Server. They are often, and incorrectly, considered to be pretty much one in the same so it is sometimes confusing to some SQL Server users. Another important security concept tied to a login and user in SQL Server is Security Identifiers (SID). This article will explain these concepts through a step-by-step demonstration. This article will focus on the Windows login and provide some specific usage scenarios.
Login vs User
A login is a security principal at the scope of the SQL Server instance, and a SQL Server instance can contain numerous databases. There are two main types of logins; Windows authenticated login and SQL Server authenticated login. Simply stated, a login allows you to connect to a SQL Server instance.
Once you are connected to SQL Server instance, you will typically need access to a particular database. To allow access to a database, this login must be mapped to a database user. Internally within SQL Server, a login is mapped and identified to a user using security identifier (SID).
With this brief definition of login and user, we will now proceed to our demonstration to learn how these security concepts are connected to each other.
Windows Authenticated Login
In the scenario when a Windows user is created in the Active Directory, it is assigned a security identifier (SID) which is used to access domain resources. SIDs are unique within their scope (domain or local) and are never reused. This domain level SID is then used by SQL Server as source principal for SID. So, we are now talking about two SIDs, one is the domain level SID and the other is the SQL Server SID. The domain level SID representation is in string which looks like GUID (i.e. S-1-5-21-124525095-708259637-1543119021), and SQL Server SID representation is in binary (i.e. 0x010600000000000901000000E286A25BE10CFF1FDF83DB82A0179E302B98EB23).
From the high level, they might seem to be different, but the SID stored in SQL Server is actually a translation from the domain SID. If this statement seems confusing, then the demonstration below would walk you through how they are connected to each other.
The configuration of our demonstration is three SQL Servers 2016 Developer Edition running Windows Server 2012 R2 Data Center. All the servers are joined to the domain controller SQLDC.
Step 1 – Create a Windows user
In Active Directory, we will create a Windows user account u007. We can execute a Windows Management Instrumentation Command-line (WMIC) from a command prompt as below to get the domain level SID for Windows user account u007.
Step 2 – Create User Defined Function
Before proceeding any further, we will execute the script below to create a user defined function ufn_StringToSID on the 3 SQL Server instances – SQLP1, SQLP2 and SQLP3
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
USE tempdb GO CREATE FUNCTION dbo.ufn_StringToSID ( @strSID VARCHAR(100) ) RETURNS VARBINARY(100) AS BEGIN DECLARE @binSID varbinary(100), @i bigint SET @binSID = CONVERT(varbinary, CONVERT(tinyint, SUBSTRING(@strSID , 3,1))) + 0x05 SET @binSID = @binSID + CONVERT(binary(6), CONVERT(tinyint, SUBSTRING(@strSID , 5,1))) SET @strSID = SUBSTRING(@strSID,7,LEN(@strSID)-6) WHILE CHARINDEX('-',@strSID) > 0 BEGIN SET @i = CONVERT(bigint, SUBSTRING(@strSID,1,CHARINDEX('-',@strSID)-1)) SET @binSID = @binSID + CONVERT(varbinary(4), REVERSE(CONVERT(varbinary, @i))) SET @strSID = SUBSTRING(@strSID,CHARINDEX('-',@strSID)+1,LEN(@strSID)) END SET @i = CONVERT(bigint, @strSID) SET @binSID = @binSID + CONVERT(varbinary(4), REVERSE(CONVERT(varbinary, @i))) RETURN (@binSID) END |
Step 3 – Add Windows user account to SQL Server
We will now add Windows user u007 into SQLP1.
|
1 2 3 4 5 6 7 |
USE [master] GO CREATE LOGIN [SQLDC\u007] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english] GO |
SQL Server provides an undocumented system function SID_BINARY() which can translate domain level SID into binary SID representation stored in SQL Server.
In this article, we will also use our, previously described, userdefined function ufn_StringToSID to convert the domain level SID into binary SID representation stored in SQL Server. Both functions accept the domain level SID as a parameter and return a binary value output.
One reason to use ufn_StringToSID is to allow an insight into the translation between domain level SID into SQL Server SID representation. SID_BINARY() output would serve as a control output to make sure the user defined function is working as it should.
The binary login SID stored in SQL Server can be retrieved from dynamic view management sys.server_principals. Both function output should match the SID retrieved from sys.server_principals.
Here, you can see that the SID stored in SQL Server is just a binary representation of the domain level SID.
Step 4 – Grant Windows user u007 privilege to a user database
When the login u007 is granted access to a database in SQL Server, it creates a database user which maps back to the instance level login. Internally in SQL Server, the Windows login maps back to the database user using the same SID value.
So, the database user name can pretty much be any name and the permission would still work fine. This is possible because the binary SID value will be the same for both the login at the SQL Server instance level and the user at the database level.
Step 5 – Windows login automatically maps SID
We can now backup and restore the [SQLShack] database to SQLP2. We will add Windows user u007 to SQL Server instance SQLP2. We do not need to grant any additional privilege on the [SQLShack] database. The Windows login will automatically map to the database the user SID in [SQLShack] database and all privileges will be maintained the same as [SQLShack] database on SQLP1.
Step 6 – Domain level SID do not change
Since the domain level SID did not change, the binary SID value in SQL Server sys.server_principals will remain the same. If we check the domain level SID and the SID stored in SQL Server on SQLP2, they would produce the same output as on SQLP1 for both the login and the database user.
If we execute a create table DDL on SQLP2, db_owner permission would allow the table to be created successfully.
Step 7 – New Windows user with same name
In some organizations, Windows user accounts are created in an incrementalnumbering fashion. A user account is dropped when an employee leaves the organization, and the same number sometimes gets re-used over time.
What happens when the user account is dropped from Active Directory and the same Windows user name is created?
Well, the Windows user will be assigned a new SID by the Active Directory. The new SID gets the next numbering and incremented by 1.
The repercussion is that the new Windows user u007 SID will not be able to connect to SQL Server SQLP1 and SQLP2 because the binary SID value stored in SQL Server would not match.
Step 8 – Restore user database and add new login on SQLP3
Now we restore SQLShack database to SQLP3 and add the new Windows user u007 here.
In this scenario, we have a mismat in SID value stored in SQL server SQLP3 between the login and the database user. A SID mismatch can happen for Windows login similarly to SQL Server login. The SID for the Windows login u007 has incremented by 1, and the database user SID value reflects the point in time when the database backup was taken on SQLP1 as indicated in the red box below.
Because the SQLP3 Windows authenticated login SID reflects the new Windows user u007, we can connect to SQL Server SQLP3. But the Windows login would not allow access to the user database. This situation sometimes provides problems when the database user name is the same as the login because SQL Server would then prompt, with an error, that the user name already exists when you try to grant privilege to the database.
The solution to fix this issue is to drop the database user from the database. Then grant the Windows login again access to the database either using T-SQL or SSMS.
Conclusion
SQL Server logins can be assigned server level permissions, and database users can be assigned database level permissions. Logins and users are mapped to each other when the SID values are identical.
A significant benefit of using a Windows user in SQL Server is the automatic mapping of SID to allow access to resources, since the SID is always the same on the scope of domain or local.
Beware of dropping and recreating a domain user account with the same account name. This could cause the unexpected issues such as mismatched SIDs in SQL Server. The best practice is not to reuse the domain user account with the same name.
Simon has over 15+ years of database design, implementation, administration and development in SQL Server. He is a Microsoft Certified Master for SQL Server 2008 and holds a Master’s Degree in Distributed Computing. Achieving Microsoft masters-level certifications validate the deepest level of product expertise, as well as the ability to design and build the most innovative solutions for complex on-premises, off-premises, and hybrid enterprise environments using Microsoft technologies.
View all posts by Simon Liew