SQL Server Security Audit (Part 1) – Server Level Audit
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:
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.
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:
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.
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:
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.
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:
Listing the credentials and what accounts they map to should be part of your audit.
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.