Linked server permissions for a sql user

Question

Linked server permissions for a sql user

Pyman

SSChampion

Points: 13830
More actions
August 7, 2012 at 8:41 am

#260440

Hi,
I created a linked server between SQLServer 2008 R2 sp1 and Mas90. I am able to pull data out of the mas90 database through my login which is a domain admin. But when I try to access data with a sql login I am getting a 'login failed for user <sqluser name>' error. The sql user is currently also a system admin on SQL Server 2008 R2.
Need your help on this.
Thanks
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

it probably depends on how you set up the linked server;

you can use the current login and pass it to the other server, if they have permissions, or you can specify to use a specific user.

how is yours set up? the image below, is how you set it up to use a specific user for anyone who touches the linked sever;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Pyman SSChampion Points: 13830 More actions · Answer 2

Lowell (8/7/2012)
you can use the current login and pass it to the other server, if they have permissions, or you can specify to use a specific user.

Mine is setup the same way as shown in the image. But what permissions are you referring to? the user is a sysadmin on SQLServer but should it have permissions on the MAS90 database too?

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Lowell SSC Guru Points: 323518 More actions · Answer 3

Sapen (8/7/2012)
Mine is setup the same way as shown in the image. But what permissions are you referring to? the user is a sysadmin on SQLServer but should it have permissions on the MAS90 database too?

in my crappy screenshot, we are talking about the MAS90 user that should be used.

i'm not familiar with it, but supposed you had the proper user in MAS90 named "MASREADONLY", as that's the "right" user that should be querying the linked server.

that is the user i would put , along with the MAS90 password, in the example area of the linked server setup.

then you can test it very easily:

--test as a normal, non sysadmin user:

EXECUTE AS USER='mydomain\lowell'

select suser_name() --it's not sysadmin any more!

--see any tables int he linked server?

exec sp_tables_ex MyLinkedserver

--so if i see or know a specific table ont he linked server, lets test it:

SELECT * FROM MyLinkedServer.Databasename.dbo.Invoices;

--testing complete, turn back into superman:

REVERT;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Pyman SSChampion Points: 13830 More actions · Answer 4

Lowell (8/7/2012)
Sapen (8/7/2012)
Mine is setup the same way as shown in the image. But what permissions are you referring to? the user is a sysadmin on SQLServer but should it have permissions on the MAS90 database too?
in my crappy screenshot, we are talking about the MAS90 user that should be used.
i'm not familiar with it, but supposed you had the proper user in MAS90 named "MASREADONLY", as that's the "right" user that should be querying the linked server.
that is the user i would put , along with the MAS90 password, in the example area of the linked server setup.
[/code]

I changed the user with the mas90 database user in the security context. And I now get a different error:

OLE DB provider "MSDASQL" for linked server "MAS90_LINK" returned message "[ProvideX][ODBC Driver][FILEIO]Table is not accessible".

Msg 7306, Level 16, State 2, Line 1

Cannot open the table "ar_customer" from OLE DB provider "MSDASQL" for linked server "MAS90_LINK".

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Lowell SSC Guru Points: 323518 More actions · Answer 5

you said if you connect to teh server as your own user, you can select from that table via the linked server, right?

that error sounds like the MAS90 user you selected doesn't have access to that table / schema.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Pyman SSChampion Points: 13830 More actions · Answer 6

Lowell (8/7/2012)
you said if you connect to teh server as your own user, you can select from that table via the linked server, right?
that error sounds like the MAS90 user you selected doesn't have access to that table / schema.

I am able to fetch data from MAS90 database if I login to SSMS with windows authentication user (which is also a domain admin). Since I cannot share this login I created a sql login with sysadmin permissions (as this is a test env) and I am logging into SSMS to fetch the data. This is when I am getting the specified errors. So what I am wondering is if this is a permissions issue for the sql login?

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Lowell SSC Guru Points: 323518 More actions · Answer 7

not sure on this one now; i'm clueless on MAS90 in this case; any hints from Google on the issue?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Pyman SSChampion Points: 13830 More actions · Answer 8

There is a user I am using in the provider string for the linked server. I am using the same user which I am pretty sure should have enough permissions on the MAS90 database. In the local server login to remote server login mappings I am using the sql user username and I am providing the user name and password from the provider string and I am also using the same username and password from the provider string for the part: for a login not defined in the above list connections will be made using this security context.

Lowell (8/7/2012)
not sure on this one now; i'm clueless on MAS90 in this case; any hints from Google on the issue?

not yet..I am still searching

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

johnsandstorm Grasshopper Points: 18 More actions · Answer 9

I was also getting the error:

OLE DB provider "MSDASQL" for linked server "MAS90_LINK" returned message "[ProvideX][ODBC Driver][FILEIO]Table is not accessible".

Msg 7306, Level 16, State 2, Line 1

I found that the permissions for the SQL Server service were running under a local account. In the SQL Server configuration manager, I changed the account for the service to a windows domain account that has access to the MAS 90 server (a separate box in my installation) and everything worked fine.