SQL Server 2008 Security Permissions – Windows / Active Directory Authentication Issues
By: Brenton Blawat
Systems Administrators around the world have been baffled by security changes with SQL Server 2005/2008 and Active Directory Authentication. Even more so the issues with traversing through layers of security groups for Windows permissions within SQL Server 2008. For example, in the initial releases of SQL Server 2008, there was a frustrating bug in which adding new domain users or groups to SQL Server 2008, you MUST add these domain users and groups as the user that installed SQL Server 2008; be it a domain or a local user. Below are two SQL Server Windows Security caveats that we found were necessary to address to provide some level of sanity to our readers.
User Security Identifiers Now in the Sys.SysLogins
One item that Microsoft snuck into SQL Server 2005 and SQL Server 2008 are the use of Security Identifiers (SIDs) in the authentication model. In the typical model of SQL User Authentication, this becomes a non issue, however, with a Windows based authentication, this can cause HUGE headaches if not planned for properly.
Security Identifiers are identification GUIDs that are tied to users and groups in local security and active directory. These values are created to ensure that if a hacker tries to hijack a username such as “Brenton” in the “Geeks” group, it rejects the hijacked username as the security identifier is required in addition to the login name. This works as an excellent method to prevent hijacking attempts, however, if an administrator accidently deletes a User or Group, a new SID is created.
SIDs are secure but also are the key to the headache. Lets say a network consists of 1050 independent SQL Servers at a branch of retails stores, like American Eagle Outfitters (they really do have databases at each breach; and they really do have 1050+ stores). If the authentication credential on all 1050 stores is “Geek_User” and a systems administrator accidently deletes this user from Active Directory, all functionality under the “Geek_User” stops. When the systems administrator realizes that he deleted this user, he will add the user “Geek_User” back into Active Directory. The issue is that the ‘”Geek_User”, while named the same, has a new SID, and the authentication into SQL Server 2008 will still fail.
Further, this issue will be extremely difficult to figure out because the User will exists within active director/local security and SQL Server. However, the authentication into SQL Server will fail to work and the services assigned to the authentication will fail to start.
*NOTE: SQL Server 2008 will not allow a User to start its services (E.G. SQL Server Agent), if the user does not have privileges within SQL Server 2008. The User or Group has to be added prior to the services starting.
One may experience an error message when starting the SQL Server Agent (being that the “Geeks_User” starts the service)** **:
http://brentblawat.files.wordpress.com/2010/10/err1_thumb.jpg?w=648&h=266
“Windows Could not start the SQL Server Agent (MSSQLSERVER) service on the Local Computer.
Error 1069: The Service did not start due to a logon failure.”
In a panic, the Systems Administrator now goes into services and re-configures the SQL server credentials of the “Geek_User”. The system now provides the misleading message of granting the logon privilege to the “Geek_user”. Now that the Service Authentication has the correct SID the error message changes. When the systems administrator starts SQL Server Agent Service, the following message is splayed:
http://brentblawat.files.wordpress.com/2010/10/err2_thumb.jpg?w=675&h=252
“The SQL Server Agent (MSSQLSERVER) service on Local Computer started and then stopped. Some services stop automatically if they are not in use by other services or programs.”
When the systems administrator checks the Windows Event Log it displays:
"Login failed for user ‘%’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors"
Correcting SID Issues
All of the above error messages are examples of broken SID error messages. The only way to correct this issue is to remove and re-add the users and groups that have been accidently deleted in SQL Server 2008. This can be done in two different ways, one, a network script on all of the servers, that utilizes SQL Server Authentication or two, a remote Powershell script.
The way to prevent a global outage due to this issue on a global scale for the “large retailer” example, is to create individual Windows User credentials for each of their 1050 stores and place them within their respective SQL Server 2008 instance. This will provide the security of Active Directory (disabling a user account if the server is stolen), and the centralized management utilizing Active Directory. While adding a Group may seem like a more efficient methodology, and it is recommended by Microsoft, it still provides a single point of failure if the credentials are accidently deleted.
The following command can be executed to drop the “Geek_User” from SQL and re-creating the “Geek_User” with ‘sysadmin’ privileges:
1:
2: DROP LOGIN [Geek_User]
3: CREATE LOGIN [Geek_User] FROM WINDOWS WITH DEFAULT_DATABASE=[MASTER], DEFAULT_LANGUAGE=[us_english]
4: EXEC sys.sp_addsrvrolemember @loginame = N'Geek_User', @rolename = N'sysadmin'
5:
6:
If Powershell is your flavor of scripting, assuming you know the computer names of each store, the Powershell Code below can be added the “Powershell Script to Determine NetBIOS – Powershell to Add SQL Server 2008 Users or Groups” code (below). Execute these commands on all of the servers affected by the issue.
Feel free to contact me if you would like me to create a looping mechanism with a CSV import.
1: # Add the user into SQL. Be sure to change the SQL User of sa and password of my password.
2: [string]$err = sqlcmd -Usa -Pmypassword -d 'master' -Q "DROP LOGON `[$user`]"
3:
4: # This variable will become populated if an error occurred; else it will remain blank.
5: if ($err) { write-host "ERROR! The following error occurred while Dropping SQL User from Master Database: $err " }
NetBIOS Based Windows Authentication Only
One of the issues that we worked with Microsoft to resolve is the authentication mechanisms for users and groups in SQL Server 2008. As of October 4th, 2010, Microsoft only supports NETBIOS based Windows authentication. This means that the credentials of “domain.root\Username” are NOT supported by the SQL Server 2008 authentication protocol stack. Further, in order to use complex domain structures such as “WinXPDevGrp.US.Microsoft.com”, it is required that an administrator resolves the NetBIOS name value of the domain prior to the addition of the User or Group.
Taking the above example, trying to resolve a group named “Geeks”, in the United States Windows XP Development Group at Microsoft, would fail if the administrator utilized the logon name of “WinXPDevGrp.US.Microsoft.com\Geeks”. The following error would appear:
http://brentblawat.files.wordpress.com/2010/10/autherr_thumb.jpg?w=890&h=290
“ Create Failed for Login ‘WINXPDEVGRP.US.MICROSOFT.COM\Geeks’. (Microsoft.SqlServer.Smo)
Additional Information:
|–> An Exception occurred while executing a Transact-SQL statement or Batch. (Microsoft.SqlServer.ConnectionInfo)
|———–> Windows NT user or group ‘WINXPDEVGRP.US.MICROSOFT.COM\Geeks’ not found. Check the name again. (Microsoft SQL Server, Error:15401)
Given the conversations with Microsoft, it unfortunately is the character of ‘period’ in the authentication string that is causing the error above. After research, I found that Microsoft addressed the ‘period’ issue in the column names as found in this knowledge base article: KB972856. SQL Server 2008 does not like the use of Periods. Even when using brackets around the name shown like “[WinXPDevGrp.US.Microsoft.com\Geeks]” or “[WinXPDevGrp.US.Microsoft.com]\Geeks]”, SQL Server 2008 will still produce the above error message.
Workarounds for Domain Authentication
No worries my friends, there are workarounds to this issue. At this point, you typically go to your Systems Administrator asking how to convert domain names to NetBIOS names. Or perhaps ask do all domains have NetBIOS Names? – The simple answer is No. NetBIOS Names are NOT required to create a domain on certain domain controllers. In fact, some organizations disable the use of NetBIOS.
Do all Microsoft Domains have NetBIOS Names? This is a little bit more of a complicated answer. When configuring Active Directory on a Windows Server 2008 Domain Controller, in order to support domain trusts, and forest trusts, Domain NetBIOS names are required. This is our speculation, but we assume this is why SQL Server 2008 utilizes NetBIOS Names for authentication. If the Domain NetBIOS Name cannot be resolved, it assumes either the NetBIOS resolution is disabled or the domain / forest trusts are not configured correctly.
Workaround #1 – Using the Login Name: “Search…” Feature
SQL Server provides a mechanism for searching the domain for users. There are a few prerequisites that are required on both the SQL Server 2008 system. The following services are required for network browsing on Windows Server 2008 that has SQL Server 2008 on it:
- Computer Browser Service – Service Name: “Browser”
- Function Discovery Provider Host – Service Name: “fdPHost”
- SSDP Discovery Service – Service Name: “SSDPSRV”
- UPnP Device Host Service – Service Name: “UPNPhost”
- The firewall rule for network discovery must be configured to enabled utilizing the following command:
- netsh advfirewall firewall set rule group=”Network Discovery” new enable=yes
From there, you can search the Active Directory Structure to find the appropriate user or group using the SQL Server 2008 GUI.
Workaround #2 – Utilizing NBTSTAT to resolve Domain NetBIOS Names
The tool of choice for system administrators is NBTSTAT. Typing the following command will allow for Domain NetBIOS name Resolution of the current domain you are connected to on a system:
- nbtstat –n
Command Result:
NetBIOS Local Name Table
NAME…………………TYPE …………………STATUS
XPDEVGRP12…………UNIQUE …………………Registered
The NetBIOS name of “XPDEVGRP12\Geeks” would then be required to be manually entered into the “Login Name:” field of the SQL Server GUI, or manually placed into the CREATE LOGON command.
Workaround #3 – Automated Powershell Domain NetBIOS Name Resolution and User Creation
We couldn’t help with my utter (moo) frustration with not being able to come up with a command within a SQL Query to resolve the Domain NetBIOS name without using “sp_configure XP_cmdshell”; a major NO NO for the Department of Defense regulatory security requirements.
Instead we developed a Powershell script to perform the following:
- Resolve the Domain NetBIOS Name
- Create the User Syntax For the SQL CREATE LOGON Command
- Issue SQLCMD commands to create the logon
Powershell Script to Determine NetBIOS – Powershell to Add SQL Server 2008 Users or Groups
1: # This script will resolve the NetBios Name of a Domain then add a user with the netbios name.
2: # Don't forget to execute: set-executionpolicy RemoteSigned
3: # in powershell prior to the use of this script
4: # Step 1: Import The Active Directory Module
5: import-module activedirectory
6:
7: # Step 2: Save the Current Directory Identity into a variable (Replace Domain.root with your domain)
8: $ident = get-addomain -identity domain.root -ErrorVariable Err -ErrorAction SilentlyContinue
9:
10: # If there is an Error Stop and Report it > Else Continue
11: if ($err) { Write-Host "ERROR! The following error occurred while obtaining NetBIOS Name: $err" }
12:
13: # Select the netbios name and put it into a string
14: [string[$netbiosname = $ident.netbiosname
15:
16: # Setup User Variable for the add. Change Geeks to the user or Group you'd desire.
17: $user = $netbiosname + "\Geeks"
18:
19: # Add the user into SQL. Be sure to change the SQL User of sa and password of my password.
20: [string]$err = sqlcmd -Usa -Pmypassword -d 'master' -Q "CREATE LOGIN `[$user`] FROM WINDOWS WITH DEFAULT_DATABASE=`[MASTER`], DEFAULT_LANGUAGE=`[us_english`]"
21:
22: # This variable will become populated if an error occurred; else it will remain blank.
23: if ($err) { write-host "ERROR! The following error occurred while creating SQL User: $err " }
24:
25: # The permission addition will execute if the above command was successful.
26: Else {
27: # Add the credential of Sysadmin to the users. This can be changed to any role.
28: [string]$err = sqlcmd -Usa -Pmypassword -d 'master' -Q "EXEC sys.sp_addsrvrolemember @loginame = N'$user', @rolename = N'sysadmin'"
29: if ($err) {
30: write-host "Error Assigning Permisssions to $user: $err"
31: }
32: }
While Microsoft did document how to do resolve NetBIOS in the Microsoft Library, they forgot a major line of code; which you see in line 5. You have to Import the Active Directory Module into Powershell, or else the command will not be recognized. I thought I would repost this code and provide it to my readers.
This should be a very simple, but useful gem of knowledge.
Happy coding!