INF: How to Set Up SQL Server 6.5 to Use Integrated Security in a Clustered Environment

• Microsoft SQL Server, Enterprise Edition, version 6.5

SUMMARY

The contents of this article have been tested on SQL Server Enterprise Edition with Microsoft Windows NT Service Pack 4.

SQL Security Manager, the utility you use for granting Windows NT users permission to SQL Server, is not Cluster Aware. This article discusses the two main issues that should be considered before implementation of a SQL Server 6.5 Cluster using Integrated Security; failover permission issues and security access based on whether or not SQL Server is clustered.

MORE INFORMATION

The first issue, failover, can be resolved by using the following practice when granting accounts access through the Security Manager. Please note that if the cluster is on Domain Controllers this is not a problem. However, if the cluster resides on Member Servers, you must grant all access to the server to Domain Groups and hence domain users. You grant this access so that if there is a failover, the users will be recognized on both nodes of the cluster and can still connect to the SQL Server (the SIDs for the user will not be different based on the node).

The second issue deals with when information is added using SQL Security Manager (before or after SQL Server is clustered). You will see different behavior in accessing SQL Server depending on when this information is added. Accounts granted through SQL Security Manager before SQL Server is clustered will be fine. However, if accounts are granted to the virtual server (while SQL Server is clustered), they will be lost when unclustering. At this point, when SQL Server is no longer clustered, these accounts must be re-added using SQL Security Manager. If logins were given to the accounts while SQL Server was clustered, the check box should be clear when re-adding the logins, otherwise errors occur.

Additional query words: prodsql xp_grantlogin, xp_revokelogin

Keywords : kbSQLServ650
Version : winnt:6.5
Platform : winnt
Issue type : kbinfo