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 →

Connection failed - SQL Server Error 772 - TCPIP Socket

Question: I've upgraded an application with a built - in Database API . When attempting to establish a SQL Server database connection this error appears - Connection failed - SQL Server Error 772 - TCPIP Socket Upon investigation the application was using the native drivers attempting to connect to a SQL Server 2016 \ Windows 2016 . As part of the testing I downloaded the ODBC 13.1 SQL Server drivers - independent of the application and tested a DSN connection to the same SQL Server - and it connected OK. I then created a DSN with native drivers and the... Read more →