Script to get Security Information of SQL Server 2005\2008\R2\2012 and Above

Script to Get Database Level,Server Level and Object Level Information with Permissions :

In my working environment I’ve used the script to find security information which should return Server Logins, Database Logins and object level permissions. I’ve used it  and thought to sharing this to you since this could be helpful to you.

Works With

  • SQL Server 2005
  • SQL Server 2008 and
  • SQL Server 2008 R2

Please go through below link to get the Scripts:

https://sqlserveradmintutorial.blogspot.com/2018/07/script-to-get-security-information-of.html

SQL Server 2012 codenamed SQL Server Denali is on the way to market by 2012. As you all know SQL Server 2012 has new features related to security, we have contained database where you are allowed to create contained users and the next feature is server level roles, other security features are not related to this article, we will cover those later. The above link scripts earlier to retrieve permissions from SQL 2005 and SQL 2008\R2 (link above) however these scripts are no longer useful for SQL Server 2012 as it won’t return the new contained user and server level role permissions.

New columns & DMV’s are added in SQL Server 2012 which will be helpful to retrieve those information’s . In addition to this I’ve added two more scripts which will return server level and database level permissions which is not given in SQL Server 2005 scripts

Works With

  • SQL Server 2012 (Denali) and above

Please go through below link to get the Scripts:

https://sqlserveradmintutorial.blogspot.com/2018/07/script-to-get-security-information-of_4.html

Cannot generate SSPI context Troubleshoot

Everyone knows that it is good practice to use a domain or service account to run the SQL service. I’m sure you do too! However, once you do the right thing and change the SQL Service account, you may start getting the following error message when attempting to connect to the sql server:

“The target principal name is incorrect.  Cannot generate SSPI context.”

The explanation, as given by Microsoft in this KB article

If you run the SQL Server service under the LocalSystem account, the SPN is automatically registered and Kerberos authentication interacts successfully with the computer that is running SQL Server. However, if you run the SQL Server service under a domain account or under a local account, the attempt to create the SPN will fail in most cases because the domain account and the local account do not have the right to set their own SPNs. When the SPN creation is not successful, this means that no SPN is set up for the computer that is running SQL Server. If you test by using a domain administrator account as the SQL Server service account, the SPN is successfully created because the domain administrator-level credentials that you must have to create an SPN are present.

What is Service Principal Name (SPN)?

SPN is a unique identifier for each service that is running on servers. With the help of SPN the clients which try to connect to the service can easily identify it. SPN for each service is registered in the Active Directory. SPNs can be registered under a Computer account or as a user account in Active Directory. The SPN for a service is created in the following format.

<service name>/<FQDN of the server>:<port number>

MSSQL/servername.domain.com:1433

How is SPN created?

When a service starts, the service tries to create the SPN (if it does not exist already) under the credentials of the service start up account. If the service is configured to run under machine accounts (Local System, Network service), SPN is created under a Computer Account  in AD. If the service is configured to start using a domain user account, the SPN is created under the user account in Active Directory.

If the service is starting under a domain account, that account should have Domain Administrator privilege in the Active Directory. Else the creation of the SPN will fail when the service starts. It is not a good security practice grant service accounts with Domain Administrator privilege. SPN can be manually added using the setspn.exe utility.

There are 3 ways to fix the problem:

  • Revert to using the Network Service or Local System account (NOT RECOMMENDED)
  • Assign the domain account to the Domain Admins group (NOT IDEAL – due to the elevated permissions)
  • Fix the problem by giving the domain account just the appropriate permissions in Active Directory. Permissions required are ( Granting these rights is not recommended )
    • ServicePrincipalName: Read
    • ServicePrincipalName: Write

We will use the 3rd option to fix the error. First, it is good practice to verify that the problem is actually due to permission issues. Log in to the server where you SQL Instance is running. Go to the error logs and look for the last time that the SQL service was restarted. You should find an error message similar to this:

Date                    10/17/2013 9:29:50 AM
Log                       SQL Server (Archive #1 – 10/17/2013 10:53:00 AM)
Source                Server
Message
The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/servername.domainname.net:1433 ] for the SQL Server service. Windows return code: 0x2098, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

This is great. At least now we have verified that the problem is related to the SPN and we are ready to apply the fix.

Log in to the server running your Active Directory service and execute the following steps:

  • Run Adsiedit.msc
  • In the ADSI Edit snap-in, expand Domain [YourDomainName], expand DC= RootDomainName, expand CN=Users, right-click CN= [YourAccountName, and then click Properties.
  • In the CN= AccountName Properties dialog box, click the Security tab.
  • On the Security tab, click Advanced.
  • In the Advanced Security Settings dialog box, select one (any) of “SELF”‘s row
  • Click Edit, Open Permission Entry dialog box.
  • Make sure Pricipal is “SELF”, Type is “Allow” and “Applied to” is “This Object Only”, in Properties section, select the properties below:
    • Read servicePrincipalName
    • Write servicePrincipalName

Click OK to apply all changes and exit the ADSI Edit snap-in

Finally, you need to restart the SQL Service(s) that use the account in question.

You can verify that the SPN has been registered successfully upon the restart by going to the SQL Server logs. You should now see an entry similar to this:

Date                    10/17/2013 10:53:58 AM
Log                       SQL Server (Current – 10/17/2013 10:54:00 AM)
Source                Server
Message
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/servername.domainname.net:1433 ] for the SQL Server service.

Connections to SQL Server should now succeed!

Another way to fix SPN issue:

There are a few ways that we can check if the SPN has been registered successfully. If your SQL Server instance is running under a domain account (which is recommended) you can run the following command to see the services that are registered. If there are no services registered for this account you will get the error message below the command.

C:\Users\test>setspn -l DOMAIN\SQLServiceAccount
FindDomainForAccount: Call to DsGetDcNameWithAccountW failed with return value 0x00000525
Could not find account SQLServiceAccount

Alternatively, you can also use the SQL Server error log to validate if the SPN has been registered successfully or not. By either filtering within the SSMS GUI or using xp_read_errorlog we can search for the string below. If found we know the SPN did not register successfully.

The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the 
SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated 
authentication to fall back to NTLM instead of Kerberos. This is an informational message. 
Further action is only required if Kerberos authentication is required by authentication 
policies.

Finally, you can contact your system administrator and have them use the ADSIEdit MMC console to manually check if the service is registered.

Now that we’ve identified the issue we can go through a couple of different options that will allow us to successfully register the SPN and use Kerberos authentication.

Option 1 – Register SPN automatically which we discussed in above

To enable the SPN to be registered automatically on SQL Server startup the service must be running under the “Local System” or “Network Service” accounts (not recommended), under a domain administrator account, or under an account that has permissions to register an SPN. The permissions required for this are the “Read servicePrincipalName” and “Write servicePrincipalName” access control settings in the Active Directory service. One thing that should be noted is granting these rights is not recommended (see http://support.microsoft.com/kb/319723) if SQL Server is clustered or if you have multiple domain controllers as latency in Active Directory replication can cause connectivity issues with your SQL Server instance.

Option 2 – Register SPN manually

To register an SPN manually we can use the Microsoft provided Setspn.exe utility. To be able to run this tool and register an SPN you need to be a domain admin or have the appropriate privileges (defined above). One other thing to note is that the -s option ensures that the SPN you are trying to create is not already defined. Here are a couple examples. The first one is for a default instance and the second is for a named instance.

setspn -s MSSQLSvc/myhost.redmond.microsoft.com DOMAIN\SQLServiceAccount
setspn -s MSSQLSvc/myhost.redmond.microsoft.com:instancename DOMAIN\SQLServiceAccount

Once you’ve picked and implemented one of these options and if necessary restarted SQL Server you can establish a new connection and run the following TSQL to check that you are now using Kerberos authentication.

select session_id,net_transport,client_net_address,auth_scheme from sys.dm_exec_connections

SSPI handshake failed Error Troubleshoot

I came across a scenario where  monitoring tool was constantly reporting “SSPI Handshake errors” and I was approached by SQL DBA team to assist in addressing these errors. Monitoring tool monitors the SQL Server Error log folder and reports these alerts. In this blog, I am covering the cause of this issue and the solution we followed to fix it:

Below errors were reported frequently in SQL Error log:

2016-02-07 12:44:22.81 Logon       Error: 17806, Severity: 20, State: 14.
2016-02-07 12:44:22.81 Logon       SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed   [CLIENT: 10.x.x.x]
2016-02-07 12:44:22.81 Logon       Error: 18452, Severity: 14, State: 1.
2016-02-07 12:44:22.81 Logon       Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: 10.x.x.x]

To share some information about SSPI: 
SSPI (Security Support Provider Interface) is  an interface between transport-level applications, such as Microsoft Remote Procedure Call (RPC), and security providers, such as Windows Distributed Security. SSPI allows a transport application to call one of several security providers to obtain an authenticated connection.

The following parameter is commonly used in connection strings for Windows authentication with trusted connection:
Integrated Security=SSPI

There can be 2 variants in SSPI errors:
“Cannot generate SSPI context “ and “SSPI Handshake Failed”

Cannot generate SSPI context:  We generally get this error when the client is trying a Kerberos authentication and that fails but it does not fall back to NTLM.
SSPI handshake failed: We get this when the user is not authenticated.

In the issue we worked on we were encountering “SSPI Handshake Failed” which indicates that the SQL Server was unable to authenticate the user.

To debug the error further, we reviewed the security logs in Event viewer on SQL Server box during the time of the issue:

An account failed to log on.
Subject:
Security ID:                      NULL SID
Account Name:               –
Account Domain:                          –
Logon ID:                         0x0

Logon Type:                                  3

Account For Which Logon Failed:
Security ID:                      NULL SID
Account Name:               sqlaccount
Account Domain:                          contoso

Failure Information:
Failure Reason:               The user has not been granted the requested logon type at this machine.
Status:                              0xC000015B
Sub Status:                      0x0

Process Information:
Caller Process ID:            0x0
Caller Process Name:     –

Network Information:
Workstation Name:        SQLclient
Source Network Address:            –
Source Port:                    –

Detailed Authentication Information:
Logon Process:                NtLmSsp
Authentication Package:              NTLM
Transited Services:          –
Package Name (NTLM only):       –
Key Length:                     0

This event is generated when a logon request fails. It is generated on the computer where the access was attempted.
The Subject fields indicate the account on the local system which requested the logon. This is commonly a service such as the Server service, or a local process such as Winlogon.exe or Services.exe.
The Logon Type field indicates the kind of logon that was requested. The most common types are 2 (interactive) and 3 (network).
The Process Information fields indicates which account and process on the system requested the logon.
The Network Information fields indicates where the remote logon request originated. Workstation name is not always available and may be left blank in some cases.
The authentication information fields provide detailed information about this specific logon request.
– Transited services indicate which intermediate services have participated in this logon request.
– Package name indicates which sub-protocol was used among the NTLM protocols.
– Key length indicates the length of the generated session key. This will be 0 if no session key was requested.

Inference:

The Security log gave us  a lot of information to process. Decoding the logs closely:

From the workstation “SQLClient”, “Contoso\sqlaccount” is trying to connect to the SQLServer box with logon type 3: Network (A user or computer logged on to this computer from the network) and the error thrown is: The user has not been granted the requested logon type at this machine.

To isolate the issue, we logged on to SQLClient box using the account “contoso\sqlaccount” and launched the udl file to connect to SQL instance:

UDL Testing

We got the same error reported in SQL Error log:
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication

Resolution:

To address the issue:

We added the account “contoso\sqlaccount” to “Access this computer from the network” local security policy (secpol.msc) on the SQL Server box and post which we were successfully able to connect to the instance from the application.

To address the SSPI Handshake failed errors, always review the security logs post enabling Audit Logon events. Security logs would give a good amount of  information needed to address this issues.

Reference:
https://technet.microsoft.com/en-us/library/cc787567(v=ws.10).aspx

Another situation of SSPI Handshake Error code 0x8009030c ::

The other day I was working in SSMS and connected to an instance.  Pretty normal there.  I later opened some window (I don’t recall exactly–perhaps a report or some db option) and was met with this error message.  I could then do nothing in the database.

SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure.  [CLIENT: 10.1.1.1].

This same error was found in the SQL Server log with the accompanying error message.

Error: 17806, Severity: 20, State: 14.

It turns out my Windows password had expired from the time I opened the instance to the time I was looking at something in the database.  Once I reset my password, I was good to go.