Best Practices: Windows Authentication Security and ScaleArc

Release	Classification	Level	DB Platform	Categories
All	How To		MSSQL	Connection Management

In relevance to connecting to MSSQL server database, microsoft typically offers two common authentication methodologies for clients to connect to database -

• SQL Authentication
• Windows Authentication

This article is intended to cover prechecks and limitations for achieving success with a windows authenticated connectivity.

WARNING : Please refer to "ScaleArc Support for AD Integration" prior to joining AD

https://support.scalearc.com/kb/articles/3117

Scenario-1 - AD authentication ON all the time through ScaleArc

Steps - a. Join AD domain "SETTINGS > System Settings > Windows AD Setup"

               note: Joining Scalearc to AD domain has few known limitations. Failing to comply to those might lead to failure.

           b. "Users & DBs > Authentication Offload" = ON 

           c. "Users & DBs > Fetch Users Auto Fetch Database Users>" = ON

           d. "Cluster Settings > ScaleArc > Windows Authentication" = ON

Scenario-2 - AD authentication ON only once through ScaleArc

Steps - a. Join AD domain "SETTINGS > System Settings > Windows AD Setup"

               note: Joining Scalearc to AD domain has few known limitations. Failing to comply to those might lead to failure.

           b. "Users & DBs > Authentication Offload" = ON 

           c. "Users & DBs > Fetch Users Auto Fetch Database Users>" = ON

           d. Once all users are fetched, Auto Fetch = OFF

           e. Unjoin Scalearc from AD domain

           f. "Cluster Settings > ScaleArc > Windows Authentication" = ON

Scenario-3 - No AD authentication at all through ScaleArc

Steps - a. "Users & DBs > Authentication Offload" = ON 

           b. "Users & DBs > Fetch Users Auto Fetch Database Users>" = OFF

           c.  Add individual Windows users by using "Add User"

           d. "Cluster Settings > ScaleArc > Windows Authentication" = ON

Scenario-4 - Authenticating directly against Database

Steps - a. "Users & DBs > Authentication Offload" = OFF

           b. "Users & DBs > Fetch Users Auto Fetch Database Users>" = OFF

           c. "Cluster Settings > ScaleArc > Windows Authentication" = OFF

 The following is a checklist for settings related to AD and ScaleArc security which are necessary for successful implementation: Functional Capabilities for ... on ...

1. Login protocol negotiation: NTLM (version 1) is very old and not supported. Only NTLMv2 is supported. Type of NTLM authentication set on Client/Server - MSSQL
2. Host name resolution: rDNS (reverse lookup) is required by AD for the ScaleArc host. Refer to PTR record creation. Add a Host (A) DNS Record Manually to a Windows DNS Server, DNS setup for ScaleArc for MSSQL
3. TDS version 7.0 or greater.
4. Setting the "Search Domain"
5. Use the ADC for the Primary NTP server
6. By default, Samba adds the ScaleArc main IP and VIPs into DNS for various services (as SRV records). ScaleArc is a Trusted Host. Ensure that the ADC does not deligate ScaleArc as a BDC (uncheck "trust this host for deligation").

Verifying or troubleshooting a login from ScaleArc (using the TSQL CommandLine Interface) How To: Test my MSSQL Connection from ScaleArc CommandLine

Using SSMS (SQL Server Management Studio How to connect from SQL Server Mgmt Studio to MSSQL Database with Windows Authenticated User

Understanding the SQL Server PORT