Extended Stored Procedure with ODBC DSN

Question

I have an extended stored procedure (written in Delphi if that makes any difference) that makes its own connection via ODBC and performs some processing - stuff that can't be done using T-SQL alone.

If I try to connect using the System DSN that is set up (e.g. named MyDataSource), the xp returns the following error:

[Microsoft][ODBC SQL Server Driver][SQL Server] Cannot open database requested in login 'MyDataSource'. Login fails.

However, if I connect using a connection string (SERVER=MyServerName;DATABASE=MyDatabaseName;etc) then it connects & the xp works.

I tested the code outside of an xp, just a Windows app, and the connection using the DSN worked. I'd expect this as it is the same way I have connected to a database for 10 years!

So I have it working but really want to know why it doesn't work using a DSN. I also tried with a user DSN but no difference.

Any ideas? Thanks.

Answer 1

Keep in mind that when the extended stored procedure is executed it is running under the login that the SQL server service is using. If the log in that SQL server does not have permissions on the network then denial errors will occur. I hope this helps.