Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Linked server permissions issue Expand / Collapse
Author
Message
Posted Tuesday, November 29, 2011 2:31 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 9, 2014 9:38 AM
Points: 1,354, Visits: 1,750
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
Post #1213570
Posted Wednesday, November 30, 2011 8:57 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 9, 2014 9:38 AM
Points: 1,354, Visits: 1,750
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
Post #1214019
Posted Wednesday, November 30, 2011 9:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:58 AM
Points: 2,696, Visits: 3,402
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
Post #1214028
Posted Wednesday, November 30, 2011 9:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 9, 2014 9:38 AM
Points: 1,354, Visits: 1,750
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
Post #1214065
Posted Wednesday, November 30, 2011 9:42 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:58 AM
Points: 2,696, Visits: 3,402
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
Post #1214067
Posted Wednesday, November 30, 2011 9:50 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 9, 2014 9:38 AM
Points: 1,354, Visits: 1,750
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
Post #1214075
Posted Wednesday, November 30, 2011 9:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:58 AM
Points: 2,696, Visits: 3,402
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
Post #1214087
Posted Wednesday, November 30, 2011 10:09 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 9, 2014 9:38 AM
Points: 1,354, Visits: 1,750
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
Post #1214098
Posted Wednesday, November 30, 2011 10:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:58 AM
Points: 2,696, Visits: 3,402
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
Post #1214103
Posted Wednesday, November 30, 2011 10:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 9, 2014 9:38 AM
Points: 1,354, Visits: 1,750
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
Post #1214105
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse