User cannot access a system DSN on Windows Server 2008

linked-serverodbcsql-server-2008windows-server-2008

We run our SQL Server services using a low privileged domain account. That account is NOT a local admin on the OS. Only access I give the user account is assigned during install of SQL: full control over its mount points and then everything else is granted by the SQL Server 2005/2008 installer.

I need to create a linked server in SQL Server 2008 to an ODBC data source. So I remoted into the computer using my domain account, which is part of a group that DOES have local admin privs to the OS. I created a system DSN and configured it to connect to another SQL Server. The DSN works perfectly when I test it. However, when I try to create the linked server, I get an error.

It appears to me that the DSN is invisible to the domain account that SQL Server is running as. It seems that this problem is only happening to me on Windows 2008 servers. Does anybody know whether there's anything that you need to do after creating a DSN to make it visible for other users to access?

Best Answer

You have to explicitly add the user's Windows login (or just DOMAIN\Domain Users if using AD) to SQL as a DBO of each relevant DB. Through Win2K3 server, a DSN-only SQL login was all that was needed, but as of Win2K8, the DSN-only SQL login alone is not enough. Once I added the Windows login for a given (restricted) user (actually DOMAIN\Domain Users in my case - they are all restricted users) to my SQL server, each user could then use the DSN (setup with its own, separate login) as had always been the case before... What a major PITA this was - thanks for the lack of any note, Microshaft!!!!

Related Solutions

Sql-server – How to add a self-referencing linked server in SQL 2008

Well I managed to do it via script as that worked without issue rather than the GUI

EXEC sp_addlinkedserver @server='DBSVR2',

                                             @srvproduct='',

                                             @provider='SQLNCLI',

                                             @datasrc='.',--the data source

                                             @provstr='Integrated Security=SSPI';

How to create a Linked Server in SQL Server 2005 to a password protected Access 95 database

After hours of struggling with this problem I finally found not one, but two solutions! I'm using SQL Server 2008 and Access 2000, but I guess the solution is the same.

Solution 1: Use OPENDATASOURCE

Use this approach when you will be accessing data on the linked server infrequently:

select * from 
OPENDATASOURCE('Microsoft.JET.OLEDB.4.0', 'Data Source=C:\MyAccessDB.mdb;User ID=Admin;Password=;Jet OLEDB:Database Password=MyDBPassword;')...MyTable

For this to work you also need to turn on the Ad Hoc Distributed Queries option:

exec sp_configure 'show advanced options', 1;
RECONFIGURE;
exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Solution 2: Use Linked Server

As you already stated, the linked server doesn't seem to work. However, there's a small gotcha that makes everything work smoothly; you need to specify the database password in the provider string like ;PWD=password.

exec sp_addlinkedserver 
    @server = 'TestLinkServer', 
    @provider = 'Microsoft.Jet.OLEDB.4.0', 
    @srvproduct = 'Access',
    @datasrc = 'C:\MyAccessDB.mdb', 
    @provstr = ';PWD=MyDBPassword'

exec sp_addlinkedsrvlogin 
    @rmtsrvname = 'TestLinkServer',
    @useself = 'FALSE',
    @locallogin = null, 
    @rmtuser = 'Admin', 
    @rmtpassword = null

Now you can query your Access database as a linked server:

select * from TestLinkServer...MyTable

Best Answer

Related Solutions

Sql-server – How to add a self-referencing linked server in SQL 2008

How to create a Linked Server in SQL Server 2005 to a password protected Access 95 database

Related Topic