Local Admin Rights Needed to Use DSNs on W2K3 Server?

[ringoschplingo]

Hi, (sorry this is a long one)

I have MSSQL 2000 (version 8.00.2187) installed on Windows Server 2K3, nothing 'special' has been done to either of these servers as far as configuration goes.

The exact details of the Windows server are Version - 5.2.3790, SP1.

In order to query an Oracle db from SQL, I've set up a linked server within SQL that uses a DSN to connect to the Oracle system. I use ADO to connect to SQL with integrated security (SSPI = True) and users can fire SQL stored procedures via an app. which query the linked server (so these SPs of course are using the DSN).

The problem is that unless the user's domain account (the context which I presume SQL is passing to Windows when using the DSN due to the fact that integrated security is being used) is a member of the local admins group on the server, they obtain a security related error message stating that access is denied. Even if the user is a member of Power Users on the server, they get the same error. However as soon as the users account is a member of local admins on they can fire the stored procedures and get data from the linked server no problems.

I can understand why you would want to place security around DSNs which may be able to connect to a datasource with elevated privileges, but conversely I don't like having to make end users admins of server boxes.

So my question - is it possible to modify our setup in some way to either 1) configure Windows to allow 'normal' users (no elevated windows privileges) to call and use DSNs in the manned described above or 2) configure SQL to not pass a users credentials when calling to Windows to use a DSN (instead perhaps passing the credentials of the SQL service), this way I could grant the right permissions to the SQL account and wouldn't end up with loads of end users being local admins on my server. If this is possible could someone describe to me the steps taken to do this.

I've spoken to someone from IT about this and he suggests opening up permissions on the registry where details of DSNs are stored. Not surprisingly I'm not overly keen on this option.

Thanks for reading this far, any suggestions greatly appreciated!

Cheers,

Ian

[Motoma]

This is a darn good question to which I have no real answer to. I have no experience (read: exactly zero experience) with DSNs, so I can only give broad suggestions on how I would go about this.

My only suggestion is to take a look at the Security tree in Enterprise Manager. Pay particular attention to the Logins and Linked Servers tabs. Thoroughly check all of the permissions for the user accounts in that area.

Sorry I could not be of more help.