Special OS, AD Accounts and SQL Server

 

Background

Windows has a few special built-in accounts and groups.  And, same goes for Active Directory.

Unfortunately, how to reference those accounts within SQL Server is not always in reach.

 

Account & Group Names

Sam Account

The SAM Database is the database that houses the local computer’s users and groups.

 

Account Name What does it mean? Account Name  Create Login
Users BUILTIN\Users  CREATE LOGIN [BUILTIN\Users]
FROM WINDOWS;
Everyone All interactive, network, dial-up, and authenticated users are members of the Everyone group  \EVERYONE  CREATE LOGIN [\Everyone]
FROM WINDOWS
Authenticated Users The Authenticated Users identity Any user accessing the system through a logon process has the Authenticated Users identity.  NT AUTHORITY\Authenticated Users CREATE LOGIN
[NT AUTHORITY\Authenticated Users]
FROM WINDOWS

 

 

 

Active Directory Domain

Account Name What does it mean? Account Name  Create Login
Domain Users <domain-name>\Domain Users CREATE LOGIN [<domain-name>\Domain Users]
FROM WINDOWS;

 

 

 

Anecdotes

Authenticated Users

Randy Franklin Smith

  1. Understanding the Authenticated Users Group
    Link

    Microsoft created the Authenticated Users group in response to fears that Anonymous logons could gain access to objects for which Everyone (another special security principal) has access. I don’t recommend using the Authenticated Users group for controlling permissions because it includes local accounts, which are a bad practice to use because you can’t centrally manage them at the domain level, and they use NT LAN Manager (NTLM) authentication rather than the stronger Kerberos. Also, the membership of Authenticated Users changes dynamically when you create a trust to another domain. When you want to give all users in a domain access to a resource, I recommend that you use the Domain Users group, which limits membership to the domain. If you need to give all users in a forest access to a resource, create a universal scope group called Forest Users and add each domain’s Domain Users group as a member.

 

Users

SS64.com

  1. Windows Built-in Users and Default Groups
    Link

    A built-in group. After the initial installation of the operating system, the only member is the Authenticated Users group. When a computer joins a domain, the Domain Users group is added to the Users group on the computer. Users can perform tasks such as running applications, using local and network printers, shutting down the computer, and locking the computer. Users can install applications that only they are allowed to use if the installation program of the application supports per-user installation.

 

 

Reference

  1. Microsoft – TechNet Magazine
    • TechNet Magazine > Tips > Windows Server 2008
      Link
    • Windows Server 2008 > Understand Implicit Groups and Identities
      Link
  2. WeaselFire Ramblings
    • Everyone isn’t everyone
      Link
  3. Exploit Database
    • Ubisoft Uplay 4.6 – Insecure File Permissions Privilege Escalation
      Link
  4. Varonis.com
    • Rob Sobers
      • The Difference Between Everyone and Authenticated Users
        Link
  5. Windows IT Pro
    • Jan De Clercq
      • What’s the scope of the built-in Authenticated Users group in a multi-forest Active Directory (AD) environment?
        Link
    • Randy Franklin Smith
      • Understanding the Authenticated Users Group
        Link
  6. Stack Overflow
    • StackExchange.com
      • Windows groups and permissions: Authenticated Users group meaning
        Link
  7. ss64.com
    • Windows Built-in Users and Default Groups
      Link

SQL Server – Kerberos Authentication – Service Principal Name & Klist

Background

One of the things to check for when experiencing Kerberos Authentication authentication errors is to determine the Service Principal Name and issue klist request.

Once klist is issued, review its output.

 

Trouble Shooting

Get MS SQL Server Instance Service Name

There are a few ways to get the SQL Server Engine’s name, here are some of them..

sys.dm_server_services

Script


select 
		  [sqlInstance]	
			= serverproperty('servername') 	

		, [computerNetbios]	
			= serverproperty('ComputerNamePhysicalNetBIOS') 	

		, [serviceName]
			= tblSDSS.serviceName

		, [serviceAccount]
			= tblSDSS.service_account

from   sys.dm_server_services tblSDSS


 

Output

Output-Production-01

ays01

Output-Dev-01

eias01-serverservices

Control Panel \ Services Applet

Access Control Panel Services Applet…

Output-Production-01

controlpanel-services-ays01

Output-Dev-01

controlpanel-services-dev01

 

 

klist

Klist / List Kerberos tickets

Get Kerberos Tickets.

Syntax


klist tickets

 

Output
Output-Image – Production-01

klist-listtickets-ayso1

Explanation
  1. On each entry we want to look for the Server Entry
  2. We want to pay attention to the Server Name.  Here are the entries we see
    • krbtgt
    • host
    • cifs
    • ldap
  3. Notice MS SQL Server service, MSSQLSvc, is not listed

Output-Image – Development-01

klist-listtickets-eiaso1

Explanation

  1. You want to look for entries that have Server MSSQLSvc listed
  2. In the Screen Shot above that  is entry #2
    1. Pay Attention to the Encrytion TYpe
    2. And, the Kdc called
      1. KDC is the Domain Controller that satisfied our request

Klist / Attempt to get Kerberos tickets

Get Kerberos Tickets.

Syntax


set _serviceClass=MSSQLSvc
set _computerName=%computername%
set _domainName=%USERDNSDOMAIN%
set _FQDN=%_computerName%.%_domainName%
set _PortNumber=1433

rem klist Syntax
rem klist get MSSQLSvc/[computer-name].[domain-name]:1433

klist get %_serviceClass%/%_FQDN%:%_PortNumber%


 

Output
Output-Image – Production-01

klist-service-ayso1-brushedup

Output-Textual – Production-01

Current LogonId is 0:0x2fe0e41b
Error calling API LsaCallAuthenticationPackage (GetTicket substatus): 0x6fb

klist failed with 0xc000018b/-1073741429: The SAM database on the Windows Server
 does not have a computer account for this workstation trust relationship.


Output-Image – Development-01

klist-service-eiaso1
Explanation

  1. You want to look for entries that have Server /MSSQLSvc

 

References

  1. > Using Active Directory Domain Services > Mutual Authentication Using Kerberos  > Service Principal Names
    Link

 

Summary

When SQL Server Engine is running as “Local System”, it is running as a machine Account, and has more leeway.

Service Principal Names (SPN) does not have to be registered.

On the Other hand, when it is running under a Service Account, SPNs either have to be explicitly registered or the Service Account has to have the authority them itself.

More much Later…

 

Microsoft – SQL Server – Authentication Error – Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)

Another day, Another (computer) problem.

One does not need to wonder much to determine why IT people have issues with “General Grumpiness”. But, let us leave that for another day.

User emailed asking if the database server is down… I checked a bit and the server appeared up.

User emailed me the exact error message “Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)”.

I tried the following:

  • Changed the “MS SQL Server” service from AD Domain Account to “Local System”, back and forth a few time
  • Used “setspn” to review spn listings
         setspn -L <server-name>
         setspn -L <AD Domain Account> | find “<server-name>”
  • Thought about using klist or/and kerbtray
  • But, could not shake the need to try “w32tm /resync”.  First time I tried it, it failed with a bunch of errors.  The second time, accessed the service palette to stop and restart “Windows Time” (w32time).  And, then issued “w32tm /resync”.
  • Viola — Now user can authenticate using Windows Authentication

Finding the solution through the means detailed above works, but a bit more scientific path would be to enable Kerberos “Event Logging”.

How to enable Kerberos event logging

http://support.microsoft.com/kb/262177/
Enabling Kerberos Event Logging on a Specific Computer

Start Registry Editor.

Add the following registry value:

      \HKEY_LOCAL_MACHINE
      \SYSTEM
      \CurrentControlSet
      \Control
      \Lsa
      \Kerberos
      \Parameters

            Registry Value: LogLevel
            Value Type: REG_DWORD
            Value Data: 0x1

If the Parameters subkey does not exist, create it.

Please remove this registry value when it is no longer needed so that performance is not degraded on the computer. Also, you can remove this  registry value to disable Kerberos event logging on a specific  computer.

The setting will become effective  immediately on Windows Server 2008, on Windows Vista, on Windows Server 2003, and on Windows XP.

For Windows 2000, you must restart the computer.

Review Event logging

Once the registry change is effected, issue the client command\request again and start reviewing the Windows Event Viewer:

For example, on a computer that has this error.

Error:- 0x25 KRB_AP_ERR_SKEW



A Kerberos Error Message was received: on
logon session
Client Time:
Server Time: 22:26:27.0000 11/19/2010 Z
Error Code: 0x25 KRB_AP_ERR_SKEW
Extended Error:
Client Realm:
Client Name:
Server Realm: LAB.COM
Server Name: LDAP/LABDC04.LAB.com/lab.com
Target Name: LDAP/LABDC04.LAB.com/lab.com@LAB.COM
Error Text:  File: 9 Line: d86 Error Data is in record data.

Interpretation:

AD Server (LABDC04) was contacted and it returned error 0x25 KRB_AP_ERR_SKEW.

Using Google came upon MS’s own explanation:

Authentication Errors are Caused by Unsynchronized Clocks

http://technet.microsoft.com/en-us/library/cc780011(WS.10).aspx

Kerberos authentication relies on the date and time that are set on the KDC and the client.  If there is too great a time difference between the KDC and a client requesting tickets, the KDC cannot determine whether the request is legitimate or a replay. Therefore, it is vital that the time on all of the computers on a network be synchronized in order for Kerberos authentication to function properly.

Clock skew can be easily diagnosed by reviewing the information in the System log.

References