Troubleshooting SQL Server Security
- Group Policy items determine account lockout settings
- The ALTER LOGIN Transact-SQL statement can unlock locked SQL Server–authenticated logins and force password changes
- Windows authentication mode disables SQL Server authentication mode
- Mixed authentication mode uses both Windows authentication and SQL Server authentication
- Verify the expiry dates of certificates when troubleshooting certificate-based security
- sys.certificates catalog view – is used to view certificate properties
- sys.endpoints catalog view – is used to view to view endpoint information
- sys.server_principals catalog view - will show whether a login is disabled and authentication type
- sys.sql_logins catalog view - will show whether a SQL Server–authenticated login is configured to use a password policy and has an expiration date
- sys.server_permissions catalog view - provides information about server-level permissions
- sys.server_role_members catalog view - provides information about role membership
- If the SQL Server Browser service is not running clients will not be able to connect to named instances that do not use fixed ports
Troubleshoot certificates and keys
- sys.asymmetric_keys Provides information about each asymmetric key, including how the key is encrypted, how the private key is encrypted, the key length, and the algorithm used.
- sys.certificates Provides information on each certificate stored in the database, including certificate name, how the private key is encrypted, certificate serial number, certificate login security identifier (SID), certificate expiration, and the date when the private key was last backed up.
- sys.key_encryptions Provides information about each symmetric encryption specified when using the CREATE SYMMETRIC KEY statement with the ENCRYPTION BY parameter. Options include encryption by symmetric key, password, certificate, asymmetric key, and master key.
- sys.symmetric_keys Provides information for every symmetric key that has been created by using the CREATE SYMMETRIC KEY statement, including the database principal who owns the key, key length, and key generation algorithm.
- sys.database_mirroring_endpoints Provides information on endpoints used for database mirroring and AlwaysOn Availability Groups. You can use this catalog view to determine the connection authentication type and the properties of any certificate used to secure the endpoint.
- sys.endpoints Provides information about each endpoint created on the instance. Includes information on endpoint protocol, payload type, and endpoint state.
- sys.http_endpoints Provides information on each endpoint that uses the HTTP protocol. Enables you to determine whether Secure Sockets Layer (SSL) is in use and the type of authentication method that has been configured for the endpoint.
- sys.service_broker_endpoints Provides information on service broker endpoints, including authentication configuration, encryption algorithm, and the identifier of any certificate used for authentication.
- sys.tcp_endpoints Provides information about the TCP endpoints in the system, including port number, IP address, and whether the port is dynamic.