SA0094 : Authentication set to Mixed Mode

Many organizations use SQL Server for critical data storage and management, and access control is fundamental to database security. Windows Authentication is more secure than allowing both Windows and SQL Server Authentication (Mixed Mode) because it relies on the network’s managed security, and benefits from the built-in security protocols, such as Kerberos.

For example:

1-- Example of possible misconfiguration
2SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS AuthenticationMode;

Mixed Mode provides backward compatibility, but it can introduce potential security vulnerabilities, such as weaker password policies. Restricting SQL Server to Windows Authentication strengthens security by minimizing external login options and leveraging domain security policies.

• Mixed Mode can expose the server to brute force attacks because SQL logins might have simpler password requirements.

• Relying on SQL Server Authentication can result in managing two separate user databases, increasing complexity and security risks.

This fix involves changing the authentication mode of your SQL Server instance to Windows Authentication to enhance security.

Follow these steps to address the issue:

1. Connect to your SQL Server instance using SQL Server Management Studio (SSMS).

2. Right-click on the server name in Object Explorer and select Properties.

3. In the Server Properties window, navigate to the Security page.

4. Select Windows Authentication mode from the authentication options.

5. Click OK to apply the changes, and restart the SQL Server service for the changes to take effect.

6. Verify the configuration change by querying the current authentication mode:

For example:

1-- Query to check the current authentication mode
2SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS AuthenticationMode;
3-- Expected result:
4-- AuthenticationMode
5-- 1 (indicating Windows Authentication Only)

The rule has a ContextOnly scope and is applied only on current server and database schema.

Rule has no parameters.

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Maintenance Rules, Security Rules

Choose an Authentication Mode

How to check SQL Server Authentication Mode using T SQL and SSMS

Other Resources