SQL SERVER – Secure by Default – The Mantra

Secure by default means giving an application the access it needs and making additional access a deliberate choice. A question at SQLSaturday Sri Lanka in 2015 brought this principle into a lively discussion about SQL Server security.

Most conference requests I receive concern performance tuning. During that event’s question-and-answer session, an attendee asked for ten security practices. I needed a moment to gather my thoughts. Afterward, the attendee sent a helpful summary of the discussion, which inspired this post.

Start with the application identity

Use a dedicated application identity with only the permissions its operations require. An application should not run as a sysadmin or database owner merely to avoid permission errors. Separate routine application work from administrative maintenance.

Review existing accounts and their dependencies before disabling unused access. Plan the change and test it. Renaming an administrator account does not prevent denial-of-service attacks, and a less obvious login name is not a substitute for restricted access.

Keep input separate from SQL code

Use parameterized commands for data values. Do not concatenate untrusted input into executable SQL. Validate input for the application’s expected type and range, but do not treat validation alone as protection against SQL injection.

When a query must select an identifier dynamically, such as a column to sort by, choose it from a fixed allowlist. Parameters represent values; they do not turn arbitrary table or column names into safe SQL syntax.

Give users clear errors without exposing connection strings, credentials or detailed internal query information. Keep the diagnostic detail in protected logs that the support team can investigate.

Protect connections and the host

Use TLS encryption with proper server-certificate validation. Check the capabilities and settings of the actual server and client driver. Do not solve a production certificate error by routinely disabling validation or trusting any server certificate.

Limit access to database files, backups and service configuration to the accounts that need it. Keep the operating system, SQL Server and client drivers maintained. Network controls should restrict which systems can reach the database service.

Review failed logins and other relevant audit events, with a clear owner for investigating them. Recording an event is useful only when someone can recognize and respond to an unexpected pattern.

Inspect the current connection without changing access

The following read-only queries list effective permissions for the current security context at server and database scope. Select the intended database first. Run the check through the identity you are reviewing; results from an administrator connection do not describe an application account.

SELECT permission_name
FROM sys.fn_my_permissions(NULL, 'SERVER')
ORDER BY permission_name;

SELECT permission_name
FROM sys.fn_my_permissions(NULL, 'DATABASE')
ORDER BY permission_name;

These lists are a starting point. They do not list every object-level permission or prove that the application has only the access it needs. Review its required operations, role memberships and object permissions, then test the application with its intended identity.

No permission is granted or removed by this example. Before changing access, identify what depends on it and verify the effect in an appropriate test environment.

What security habit has made the biggest difference in your environment? Share a practical example that other readers can apply.

Least privilege is not a one-time checkbox, it is a practice to maintain as the application changes.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.