Login properties
Default database:
When logins are created at the server level, a default database is supposed to be assigned. This is the database the user will automatically connect to once his credentials are verified. If the default database is not specified explicitly, it is mapped to the master database. Since the master database has a guest account (more about guest accounts later), the user is able to login successfully and find himself in the context of the all-important master database.
In my opinion, this is not an ideal practice as far as security is concerned. A login account – whether representing a person or an application – should always have a non-system database assigned to it as the default database. The login can then be mapped to a user account in the default database. To find out which logins have master as the default database, you can execute a query like the following:
USE master
GO
SELECT name as LoginName, type_desc AS LoginType
FROM sys.server_principals
WHERE type <> ‘R’ — Excludes Server Role
AND name NOT LIKE ‘##MS_%’ — Excludes system level certificate mapped logins
AND name <> ‘sa’ — Excludes the obvious system administrator
AND default_database_name = ‘master’
Password policy:
From SQL Server 2005, native SQL Server login accounts can also be subject to Windows password policies such as complexity, length, expiration rules etc. – provided SQL Server is hosted on Windows Server 2003 or latter. This is generally a good idea because it allows you to enforce the same level of password restrictions as it is applied in Windows.
You can check if there are logins in your server that do not have any password policy attached to them. This does not necessarily mean a security violation though; there may be business rules in your organisation that prohibits assigning any password policy to SQL Server native logins. However, if there are no such rules, it is worthwhile to incorporate this check in your audit report.
SELECT
a.name AS SQL_Server_Login,
CASE b.is_policy_checked
WHEN 1 THEN ‘Password Policy Applied’
ELSE
‘Password Policy Not Applied’
END AS Password_Policy_Status,
CASE b.is_expiration_checked
WHEN 1 THEN ‘Password Expiration Check Applied’
ELSE
‘Password Expiration Check Not Applied’
END AS Password_Expiration_Check_Status
FROM sys.server_principals a
INNER JOIN sys.sql_logins b
ON a.principal_id = b.principal_id
Disabled or locked-out logins:
Next, you may want to list the logins that are disabled or locked out. Executing the following query will help you here:
SELECT name AS Disabled_LoginName
FROM sys.server_principals
WHERE is_disabled = 1
Orphan logins:
Sometimes you may find a user’s Active Directory account has been deleted but it still exists as a login in your SQL Server. This happens because when the AD account is deleted, it does not automatically drop the associated trusted accounts from the SQL Server(s) in the network. Essentially, one or more database server is then left with an “orphan” login account. Although as far as the Windows domain controller is concerned, the user or group does not exist, you still need to know if your SQL Server thinks otherwise.
Executing the following query will give you a list of non-existent Windows accounts and groups that were given access to the SQL Server.
EXEC sp_validatelogins
Linked server logins and mapped accounts
Linked servers are widely used in distributed database applications. They provide access to data sources outside SQL Server. An external data source can be another instance of SQL Server or a different vendor’s product such as Oracle or DB2.
When a SQL Server query accesses external data using a linked server, it generally needs to authenticate itself to the remote data source. The authentication process generally happens in one of two ways: either the query uses an account already defined in the remote database or it can map a local SQL login account to a remote user account.
In the first case, SQL Server directly uses the remote server’s account to validate itself. In the latter scenario, a local login maps to (and can impersonate) a user account in the remote database server.
A third way of accessing remote data is to use the account credentials of the user account running the query. Obviously in such cases the user account running the query locally needs to have access to the remote server as well.
You can inspect the security property of each linked server to see how connections are being made.
Alternately, you can run a query:
SELECT a.name AS Linked_Server_Name,
b.remote_name AS Connecting_as_Remote_User
FROM sys.servers a
INNER JOIN sys.linked_logins b
ON a.server_id = b.server_id
WHERE b.uses_self_credential = 0
As with other findings, document your results. If connections are made through the remote server’s system administrator credentials (“sa” or the “SYS” user), remember to ask why.
Server credentials
Credentials are created to provide native SQL Server login accounts access to external resources such as the file system or Windows. A credential contains a user name and password and typically corresponds to a Windows user account. When a credential is mapped to a SQL Server login, the login uses the privileges of the credential’s underlying Windows account. You can view or create credentials from within SQL Server Management Studio.
Alternately, you can use the sys.credentials catalogue view:
SELECT * FROM sys.credentials
Listing the credentials and what accounts they map to should be part of your audit.
Proxy accounts
A proxy is a layer of abstraction above a credential. In fact credentials are mainly created for proxy accounts. You create a proxy that maps to an existing credential and the credential in turn corresponds to a Windows user account.
Proxies are used by SQL Server jobs. SQL Server jobs run within the Agent service framework and proxies are a way to give a job’s step access to various subsystems outside SQL Server. Examples of such subsystems are the operating system command shell, SSIS packages, Analysis service commands or the replication engine.
When a job’s step runs under the proxy account, the Agent service accesses the external subsystem by impersonating the underlying Windows account.
A proxy can have access to multiple subsystems. However, if a user (for example developers, DBAs) needs to use the proxy, she needs to have access to it. Like credentials, you can create or view proxies for different subsystems from the SQL Server Management Studio.
