November 29, 2011 at 2:31 pm
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
November 30, 2011 at 8:57 am
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
November 30, 2011 at 9:03 am
Can you list what your current settings are for the server options on that linked server?
Thanks,
Jared
Jared
CE - Microsoft
November 30, 2011 at 9:37 am
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
November 30, 2011 at 9:42 am
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
Jared
CE - Microsoft
November 30, 2011 at 9:50 am
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
November 30, 2011 at 10:00 am
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
Jared
CE - Microsoft
November 30, 2011 at 10:09 am
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
November 30, 2011 at 10:11 am
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
Jared
CE - Microsoft
November 30, 2011 at 10:16 am
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
November 30, 2011 at 10:20 am
Sapen (11/30/2011)
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.
Go to the security section, check the "be made using this security context" and enter in the credentials used to log into the remote server. Hopefully that will work.
Jared
Jared
CE - Microsoft
November 30, 2011 at 10:21 am
I have tried running the queries both the ways and still no luck
SELECT count(*) FROM MAS90_LINK...AR_Customer
SELECT * FROM OPENQUERY(MAS90_LINK, 'SELECT count(*) FROM AR_Customer')
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 30, 2011 at 10:22 am
In the security part of the linked server setup, for kicks and giggles specify the credentials under "Be made using this security context" and see if you get the same error.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 30, 2011 at 10:22 am
So should these credentials be added to the sql server too?
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 30, 2011 at 10:24 am
Sapen (11/30/2011)
So should these credentials be added to the sql server too?
No it is passing these credentials to the remote server.
Jared
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 67 total)
You must be logged in to reply to this topic. Login to reply