Friday, March 30, 2012

OPENQUERY and Windows Authentication

We have a SQL Server and an Oracle Instance. I have been using OPENQUERY to run queries on SQL Server to compare datasets on SQL Server with those in Oracle. Yesterday, I switched from using a SQL Server login to using Windows Authentication.
My OPENQUERY returned Msg 7399, "Access Denied". So, I added a new linked server login for my windows username:
EXEC sp_addlinkedsrvlogin
@.rmtsrvname = 'ORAINST',
@.useself = false,
@.locallogin = 'NTDOMAIN\NTGROUP',
@.rmtuser = 'ORAUSER',
@.rmtpassword = 'ORAPWD'
I continued getting the same error, after monkeying with the case of my @.loginlogin info and switching my NTUSER for NTGROUP; I finally opened up the login to all users on the server:
EXEC sp_addlinkedsrvlogin
@.rmtsrvname = 'ORAINST',
@.useself = false,
@.locallogin = NULL,
@.rmtuser = 'ORAUSER',
@.rmtpassword = 'ORAPWD'
I still get the same error. The examples in BOL suggest that setting up a linked server login for a windows user should be the same as for a SQL user. Any ideas what I should try next?You cannot create a mapping for a group...only a user.

But you seem to suggest that even that is not working.
Can you provide the complete error message. 7399 usually is followed by an error from the provider.

This might be a bit obvious but make sure the local login that is being used on the server is infact setup with the login mapping. sys.linked_logins view should give the current settings. Also make sure the oracle user name and password are correctly setup in the sytem catalogs.

No comments:

Post a Comment