February 15, 2013 at 8:35 am
I am trying to select from a table that is stored on a different server, I am using opendatasource. When I run the query, I get:
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI10" for linked server "(null)" does not contain the table ""TESTTBL"."dbo"."TBLNAME"". The table either does not exist or the current user does not have permissions on that table.
The select statement is as following:
select *
from OPENDATASOURCE('SQLNCLI',
'Data Source=DESTSERVR\INSTANCE;Integrated Security=SSPI').[TESTTBL].dbo.[TBLNAME]
Both database and table do exist on the destination server, I tested the same query from another server and it works. I did query all environments sysservers, and they are all setup identical, please advice.
Lava
February 15, 2013 at 9:17 am
Check that the Windows account you're using to log into the SQL Server with has a login and permissions to select from that table on the remote server. If it does then make sure the server's can reach each other, i.e. that network connectivity is possible.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 15, 2013 at 9:27 am
Forgot to mention that when I run the queries on different instances same box, it works... However, it does not work when I do distributed query, from one box to another...
February 15, 2013 at 9:49 am
opc.three (2/15/2013)
Check that the Windows account you're using to log into the SQL Server with has a login and permissions to select from that table on the remote server. If it does then make sure the server's can reach each other, i.e. that network connectivity is possible.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 15, 2013 at 11:13 am
When I go to linked server, I can see the system catalogs. Below is how I created the the linked server:
EXEC sp_addlinkedserver
@server=N'Webdev',
@srvproduct=N'',
@provider=N'SQLNCLI',
@datasrc=N'DESTSERVR\INSTANCE';
The security is set to "Ba made using the login's current security context.
My account is set as admin to the server on both nodes.
Lava
February 15, 2013 at 11:45 am
lsalih (2/15/2013)
When I go to linked server, I can see the system catalogs. Below is how I created the the linked server:EXEC sp_addlinkedserver
@server=N'Webdev',
@srvproduct=N'',
@provider=N'SQLNCLI',
@datasrc=N'DESTSERVR\INSTANCE';
The security is set to "Ba made using the login's current security context.
My account is set as admin to the server on both nodes.
Lava
Linked Servers are not in play when using OPENDATASOURCE. If you have a working Linked Server then maybe try this instead:
select *
from [DESTSERVR\INSTANCE].[TESTTBL].dbo.[TBLNAME]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 15, 2013 at 11:50 am
My mistake, I am not using openquery but opendatasource... I got confused as I was trying to see what the issue is... Anyhow, in any cases, security might not be an issue as I have admin access to all boxes.
February 15, 2013 at 12:02 pm
Who said anything about OPENQUERY? You do not need OPENDATASOURCE to work with a Linked Server.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 15, 2013 at 12:04 pm
You are correct, it is security... I issued same statement this time using user id and password to see if it works, and yes it did give me results:
select *
from OPENDATASOURCE('SQLNCLI',
'Data Source=DESTSERVR\INSTANCE;user id =test;password=psw' ).[TESTTBL].dbo.[TBLNAME]
The question I have is that if I am running the query, and my windows account is admin on both boxes, why isn't it working then!?
Thank you.
Lava
February 15, 2013 at 12:05 pm
I said I GOT CONFUSED 🙂 No one said anything 🙂
February 15, 2013 at 12:55 pm
The information you have provided does not compute. Are you sure your login has access to that table?
What does this return?
SELECT *
FROM OPENDATASOURCE('SQLNCLI', 'Data Source=DESTSERVR\INSTANCE;Integrated Security=SSPI').TESTTBL.sys.tables
WHERE name = 'TBLNAME';
What about when using a username and password?
SELECT *
FROM OPENDATASOURCE('SQLNCLI', 'Data Source=DESTSERVR\INSTANCE;user id=test;password=psw').TESTTBL.sys.tables
WHERE name = 'TBLNAME';
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 19, 2013 at 6:52 am
The query works when I pass it user id and password, but it does not work when I use integrated security. Any idea how to get integrated security to work?
February 19, 2013 at 7:19 am
Define "does not work." Error message? Empty resultset? Remember, I cannot see what you see 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply