2

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?

squillman
  • 37,618
  • 10
  • 90
  • 145
Ra Osolage
  • 173
  • 2
  • 2
  • 10
  • My first question would be, what kind of data source do you have that you have to go through a system DSN for a linked server? – squillman Oct 02 '09 at 17:40
  • I see where you're going with your question, squillman. However, this same scenario comes into play when trying to execute an SSIS package the references a system DSN. It's not really a problem with creating a linked server itself. Rather, the problem is that the SQL Server logon account can't access the DSN.. :-/ – Ra Osolage Oct 02 '09 at 19:25
  • Have you created a System DSN instead of a User DSN? – Nissan Fan Oct 06 '09 at 21:10
  • It's definitely a System DSN. – Ra Osolage Oct 09 '09 at 12:37
  • Did you test the DSN entry to make sure it's working? – Saif Khan Oct 19 '09 at 13:43
  • Hi, Saif. I did test the DSN with the account that I used to create it. No problems there. Is just the account that SQL Server is running as that I'm having problems with... – Ra Osolage Oct 23 '09 at 18:14
  • SAME HERE! Googling got me this and one or two other unanswered posts, but NO ONE has ANY answers. When we first setup the Win2K8 server with MSSQL, we had this issue, but we had also just migrated and after we ran commands in the Query Analyzer to reconnect the logins and users for the DBs to match the new domain, it seemed to worked fine, so I chalked it up to bad login/user settings after migration, or so I thought... However, the 08/09/2011 updates came and we rebooted the server and now this same maddening issue has come back. Running those same commands NO LONGER WORKS. –  Aug 12 '11 at 07:12

3 Answers3

1

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!!!!

Bill
  • 11
  • 2
0

Ensure that you're creating 32-bit DSNs when you need to create 32-bit DSNs, and not 64-bit ones. There are two versions of the ODBC tool on a 64-bit system. The 64-bit one opens up by default. Try manually navigating to the C:\Windows\SysWOW64\odbc32ad.exe tool and creating the DSNs there.

The confusion is an unfortunate side-effect of trying to maintain backwards compatibility.

Ryan Ries
  • 55,011
  • 9
  • 138
  • 197
0

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?

From what I am reading on google it appears that this behavior is a result of the UAC. You could choose to disable the UAC on that particular server.

I am not sure if there is a way to allow this without disabling the UAC.

Zoredache
  • 128,755
  • 40
  • 271
  • 413
  • That sounds like an interesting proposal @Zoredache. But I'm not sure what a UAC is or whether that is my job function. Will have to research that. Can you give me more insight? – Ra Osolage Oct 09 '09 at 12:39
  • 2
    UAC is a thing on Windows Vista/7/2008 that tries to protect users from doing stupid things... It has some unpleasant side-effects when it is enabled. See (http://en.wikipedia.org/wiki/User_Account_Control). – Zoredache Oct 09 '09 at 16:15
  • can you please link to your source you found on google that explains this behavior is caused by the UAC :) – Nick Kavadias Oct 19 '09 at 11:52
  • I had our Windows admin turn off UAC on this server. That did not seem to help at all. Any other ideas? – Ra Osolage Oct 23 '09 at 18:15