Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Linked server permissions issue


Linked server permissions issue

Author
Message
sasken
sasken
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1426 Visits: 1915
Hello everyone,

Hope all is well.

I have recently setup a linked server from sql server 2005 to mas90 providex database and it works fine. I am able to fetch records from the providex database by issuing a select statement from the server where I have the mas90 and sql server existing. But I am getting an error when I connect to that SQL server remotely from my laptop and issue the same select statement. I have pasted the error below:

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

I need your inputs if anyone has come accross with such a scenario. I am using the same windows authentication to connect to the DB Server from SSMS from my laptop instead of doing a RDP. It works from RDP but not from my laptop.

Thank you.

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
sasken
sasken
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1426 Visits: 1915
Any updates please.

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
SQLKnowItAll
SQLKnowItAll
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2798 Visits: 3682
Can you list what your current settings are for the server options on that linked server?

Thanks,

Jared

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
sasken
sasken
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1426 Visits: 1915
I have
Data Access set to True
Use Remote Collation set to True
and all the others set to False
with a blank in the collation name and 0's for Connection timeout and query timeout

I am having the default settings I believe.

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
SQLKnowItAll
SQLKnowItAll
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2798 Visits: 3682
Hmm... Try enabling RPC out. Also, is the login from your SSMS on your machine the same that you use to login to SSMS on the server?

Jared

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
sasken
sasken
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1426 Visits: 1915
Thanks for you inputs Jared.

I have just enabled the RPC out and executed my query and I end up getting the below 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".

Yes I am the SYSADMIN and I am using the same windows authentication to connect to the SQLServer with linked server from my laptop through SSMS(from where I am getting the error) and if I do an RDP to the remote Server and connect then I am able to fetch the records. Although I am not able to view the tables from my laptop as well as actual server under the catalogs of the 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
SQLKnowItAll
SQLKnowItAll
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2798 Visits: 3682
Sapen (11/30/2011)
Thanks for you inputs Jared.

I have just enabled the RPC out and executed my query and I end up getting the below 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".

Yes I am the SYSADMIN and I am using the same windows authentication to connect to the SQLServer with linked server from my laptop through SSMS(from where I am getting the error) and if I do an RDP to the remote Server and connect then I am able to fetch the records. Although I am not able to view the tables from my laptop as well as actual server under the catalogs of the linked server(MAS90_Link).



Try also enabling RPC and make sure the distributed transaction option is TRUE as well. I can't find to much info on these, but I believe that RPC may have something to do with remotely logging into the instance and then accessing a linked server.

Jared

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
sasken
sasken
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1426 Visits: 1915
Forgot the Mention that I am using SQL Server 2005 Standard Edition. I have enabled the RPC but still ended up getting the same error. I dont see the distributed transaction at the server options but I do see it at the sqlserver properties unchecked "Requires distributed transaction for server-to-server communication".

I just enabled it and ran my queries and ended up with same error again.

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
SQLKnowItAll
SQLKnowItAll
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2798 Visits: 3682
Sapen (11/30/2011)
Forgot the Mention that I am using SQL Server 2005 Standard Edition. I have enabled the RPC but still ended up getting the same error. I dont see the distributed transaction at the server options but I do see it at the sqlserver properties unchecked "Requires distributed transaction for server-to-server communication".

I just enabled it and ran my queries and ended up with same error again.


Hmm... What credentials are you passing to the linked server? Are you impersonating? Sending explicit creds?

Jared

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
sasken
sasken
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1426 Visits: 1915
I havent setup anything in the security part of the linked server properties. But in the provider string, I am using the username and password to connect to the mas90 providex database.

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search