linked server permissions for a sql login

Question

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

Answer 1

I pasted a wrong error. The actual error message is:

OLE DB provider "MSDASQL" for linked server "<link server>" returned message "[ProvideX][ODBC Driver][FILEIO]Table is not accessible".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT * FROM ar_customer" for execution against OLE DB provider "MSDASQL" for linked server "<link server>". 

Answer 2

A SQL Server login cannot directly pass through to another server.  Unlike a domain login, whose scope is the domain plus any trusts, a SQL Server login's scope is the server. 

What you can do is create a mapping that the login can use.  To see the dialog, right click on the Linked Server definition, then choose the Security tab.  You will see the Local Login grid at the top of the panel.  You can easily do one of two things:

1. Define the same login name and password on the remote server.  Then enter your local login in the grid and click on the Impersonate box.
2. Enter your local login in the grid, then a remote login and its password.  Then your login will login remotely using the other login.

The overall impact of this for other logins can be controlled by the 4 radio buttons below.  Choose the most appropriate for your purpose.

RLF

Answer 3

Thanks for the reply Russell. I have tried that and I am getting the following errors:

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". 

Answer 4

And you must grant the remote login rights to the table.  E.g. on the other server, in the proper database 

GRANT SELECT ON ar_customer TO LocalLogin

The Linked Server does not convey any rights to data, it merely opens a pathway for the communication to happen.  The needed rights still need to be granted.

RLF

PS - You have this thread marked as a discussion, but it is really a question.

Answer 5

And you must grant the remote login rights to the table.  E.g. on the other server, in the proper database 

GRANT SELECT ON ar_customer TO LocalLogin

The Linked Server does not convey any rights to data, it merely opens a pathway for the communication to happen.  The needed rights still need to be granted.

RLF

PS - You have this thread marked as a discussion, but it is really a question.

Thanks again...If the other server was a sql server too then I would have already done that, but the linked server is between a SQL Server 2008 R2 and a Providex Mas90 database...I am not sure if the above grant statements work on the MAS90 database...Any Ideas?

Answer 6

I see this discussion from Sapen back in late 2011 and picked up again in July, 2012.   Is this you?http://www.sqlservercentral.com/Forums/Topic1213570-391-7.aspx

I ask because the author seem to imply having found a solution at one point.

In any case, it is a security issue.  If you have not found out how to map your SQL Server login to a Providex Mas90 database I am not able to figure it out either.   I do have an observation:

For some linked servers (e.g. SQL Server to MySQL) we have had to configure an ODBC data source that has the MySQL connection information.  Then the linked server refers to the ODBC data source.  Perhaps something like that is what you need. 

How does the Providex documentation describe connectivity issues such as these?

RLF

Answer 7

SQL Server is just displaying the error from ProvideX.  You need to use a proper login for the ProvideX database with proper rights.