English

How To: Connect to SQL Server using NT Authentication (DBAccess)

Procedure

How do I configure Arcview to connect to a SDE database on SQL Server using NT authentication?

Answer:

Using Windows NT authentication to access SDE on SQL Server 7.x is possible with Arcview 3.2 and Database Access 2.0. This requires a custom SDE connection be created through Avenue since the user interface currently does not allow this. An example line of code is listed below:

theCon =
SDEConnection.Make("tucson:esri_sql","hans2101","AVWORLD\tom2275","thepass")
SDEConnection.Make("tucson:esri_sql","hans2101","AVWORLD\tom2275","thepass")

The 4 parameters for the sdeconnection.make request are:

1) the database name and instance
2) data source name (DSN) on the server
3) domain\username
4) password

All parameters are required. You will also need to have the following configured:

1) SQL Server database should have the NT user added as a login, with the appropriate access rights to databases and tables (remember to change the permissions on the F and S tables for SDE layers). Note that the only way to change databases using NT authentication is in the DSN or in SQL Servers user properties. The database cannot be selected from within Arcview Database Access, like with SQL Server authentication.

2) A system DSN on the machine where SQL Server is installed and running needs to be configured for NT authentication- note that the only parameter that may change here is the default database. The name of this DSN is the second parameter in the SDEConnection.make request.

A special note for logins that have sysadmin (dbo) privileges in SQL Server:

You are dbo in SQL Server if:

1. you belong to the sysadmin FIXED SERVER ROLE.
2. You belong to the dbcreator FIXED SERVER ROLE and you created the database. For example, SDE is in the dbcreator FIXED SERVER ROLE and creates the SDE database - then SDE is dbo and anything that SDE creates is owned by dbo.
3. you are an NT administrator and log into SQL Server.

This may cause problems if when descriptive information is returned on SDE tables in Arcview Database Access. For example, "AVWORLD\tom2275" has been granted sysadmin privileges and wants to create and manipulate some data in SDE using Arcview Database Access. Any tables he creates have the username "dbo", but according to the SDEConnection the username is "AVWORLD\tom2275". The user should know this or use the username "dbo" as the login name if they have the privileges.