How to troubleshoot SPN and SQL Server Windows return code: 0x2098, state: 15

Question:I see this error in the SQL Server Error Logs . How can I troubleshoot this issue? The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/myserver.net:50234 ] for the SQL Server service.Windows return code: 0x2098, state: 15. The start up account for SQL Server is a dedicated start up account Answer: As a starting point let's look at what MSDN has to say " To use Kerberos authentication with SQL Server requires both the following conditions to be true: The client and server computers must be part of the same Windows domain, or... Read more →

How to check Enforce password expiration is set for SQL Login

Question: We had an unexpected application outage due to a SQL Login which had Enforce password expiration set . Unfortunately this was not being monitored, and led to a situation where the password had expired and the application was able to logon onto the database. It's the sql server team security policy to not have SQL Logins set with MUST_CHANGE ON. Part of enforcing and monitoring the policy includes incorporating certain checks in daily sql server security violations reports. What is the sql code to execute this check and to identify if any SQL Logins have Enforce Password Policy and... Read more →

How to check the SQL sa Login is disabled

Often organisations have a SQL server security policy dictating the 'sa' login is disabled. There is some sound reasoning behind this policy. The primary reason is to decreases the surface area available to attack - and is a common principle of security. When this is a requirement - I'll add some code to disable 'sa' as part of the build and certification process. But , from time to time - the 'sa' login will be re enabled, so it's important to build an alerting or reporting mechanism identifying the exception. The sql statement to check if 'sa' is disabled. SELECT... Read more →

How to start SSMS as Administrator

Sometimes you find yourself in a situation where you are logged onto a server via RDP - and want to start SQL Server Management Studio (SSMS) as another local login. I'm not talking about the actual SQL Server login process - but actually starting SSMS as another login. Why would you want to do this? Let's say you've logged onto a server with a particular login - but that login may not have permissions to start SSMS. Or you may have BUILTIN\Administrators enabled on the SQL Server , therefore you may need to initiate SSMS in the context of a... Read more →

Difference between ORIGINAL_LOGIN() and CURRENT_USER

ORIGINAL_LOGIN() and CURRENT_USER are useful functions in SQL Server , used to report on identities within SQL Server sessions. The purpose of the ORIGINAl_LOGIN() is to return the value of the login which made the original connection to SQL Server. CURRENT_USER - Returns the name of the current user. This function is equivalent to SELECT USER_NAME(). The definitions sound similar but there are some differences. When using CURRENT_USER , the value will change according to different impersonations used. In this example , we are using the EXECUTE AS functionality - which allows a login to execute code as another login... Read more →