Distributed Query Connection Issues

I have the following situation: I have a database on ServerA and a database
on ServerB. My client app connects to ServerA and uses a stored procedure to
retrieve a dataset from both ServerA and ServerB.
While developing the app (using SQL 2K Dev on my workstation), I used a
linked server approach. I linked ServerB and refered to the database as
ServerB.DBName.dbo.TableName and it worked fine.
I'd like to use trusted connections and my users will have SQL server access
to both servers.
Here is the problem:
If I install the app on another workstation (i.e. not on the one running
ServerA), the I get:
Msg 18452
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Using Query Analyser from the third workstation I can connect to both
serverA and ServerB using Windows Authentication, with both Named Pipes and
TCP/IP protocols.
What am I doing wrong?
Thank you very much for your help.
The issue you are hitting is sometimes called a double hop.
You are wanting to pass Windows credentials from one server
to another. The following KB article explains the issue and
still applies to SQL Server 2000 if you don't have kerberos
enabled and account delegation setup. The article specifies
SQL 7 as you can't get around this on SQL 7 and have to use
other methods to not hit issues with double hops.
PRB: Message 18456 from a Distributed Query
http://support.microsoft.com/?id=238477
Under SQL Server 2000, If you want to use Windows
authentication in this scenario, you need to use Active
Directory, enable kerberos and setup account delegation.
You can find more information in SQL Server books online
under the topic Security Account Delegation
-Sue
On Mon, 10 Jan 2005 10:55:01 -0800, "Konstantin"
<Konstantin@.discussions.microsoft.com> wrote:

>I have the following situation: I have a database on ServerA and a database
>on ServerB. My client app connects to ServerA and uses a stored procedure to
>retrieve a dataset from both ServerA and ServerB.
>While developing the app (using SQL 2K Dev on my workstation), I used a
>linked server approach. I linked ServerB and refered to the database as
>ServerB.DBName.dbo.TableName and it worked fine.
>I'd like to use trusted connections and my users will have SQL server access
>to both servers.
>Here is the problem:
>If I install the app on another workstation (i.e. not on the one running
>ServerA), the I get:
>Msg 18452
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL
>Server connection.
>Using Query Analyser from the third workstation I can connect to both
>serverA and ServerB using Windows Authentication, with both Named Pipes and
>TCP/IP protocols.
>What am I doing wrong?
>Thank you very much for your help.

Distributed Query Connection Issues

I have the following situation: I have a database on ServerA and a database
on ServerB. My client app connects to ServerA and uses a stored procedure to
retrieve a dataset from both ServerA and ServerB.
While developing the app (using SQL 2K Dev on my workstation), I used a
linked server approach. I linked ServerB and refered to the database as
ServerB.DBName.dbo.TableName and it worked fine.
I'd like to use trusted connections and my users will have SQL server access
to both servers.
Here is the problem:
If I install the app on another workstation (i.e. not on the one running
ServerA), the I get:
Msg 18452
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Using Query Analyser from the third workstation I can connect to both
serverA and ServerB using Windows Authentication, with both Named Pipes and
TCP/IP protocols.
What am I doing wrong?
Thank you very much for your help.The issue you are hitting is sometimes called a double hop.
You are wanting to pass Windows credentials from one server
to another. The following KB article explains the issue and
still applies to SQL Server 2000 if you don't have kerberos
enabled and account delegation setup. The article specifies
SQL 7 as you can't get around this on SQL 7 and have to use
other methods to not hit issues with double hops.
PRB: Message 18456 from a Distributed Query
http://support.microsoft.com/?id=238477
Under SQL Server 2000, If you want to use Windows
authentication in this scenario, you need to use Active
Directory, enable kerberos and setup account delegation.
You can find more information in SQL Server books online
under the topic Security Account Delegation
-Sue
On Mon, 10 Jan 2005 10:55:01 -0800, "Konstantin"
<Konstantin@.discussions.microsoft.com> wrote:

>I have the following situation: I have a database on ServerA and a database
>on ServerB. My client app connects to ServerA and uses a stored procedure t
o
>retrieve a dataset from both ServerA and ServerB.
>While developing the app (using SQL 2K Dev on my workstation), I used a
>linked server approach. I linked ServerB and refered to the database as
>ServerB.DBName.dbo.TableName and it worked fine.
>I'd like to use trusted connections and my users will have SQL server acces
s
>to both servers.
>Here is the problem:
>If I install the app on another workstation (i.e. not on the one running
>ServerA), the I get:
>Msg 18452
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL
>Server connection.
>Using Query Analyser from the third workstation I can connect to both
>serverA and ServerB using Windows Authentication, with both Named Pipes and
>TCP/IP protocols.
>What am I doing wrong?
>Thank you very much for your help.

Distributed query against loopback linked server is not supported

I receive the following error message when I run a distributed query against
a loopback linked server in SQL Server 2005:
"The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled
the distributed transaction."
To resolve this problem, I was told that running a distributed query against
a loopback linked server is not supported in SQL Server 2005. And I am
suggested to use a remote server definition (sp_addserver) instead of a
linked server definition to resolve this problem. (Although this is only a
temporary resolution, which will deprecate in Katmai)
However, I run into another problem when I use the remote server definition.
I receive the following error message:
"Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'ServerName\SQL2005' because '' is not defined
as a remote login at the server. Verify that you have specified the correct
login name."
Could anyone please help me out?
(I include the reproduce steps for the first error message, followed by my
resolution that generates the second error message)
====== Reproduce steps for the first error message
======
1. On the ComputerA\InstanceA instance, run the following statement to
create a database and a table:
CREATE DATABASE DatabaseA
GO
USE DatabaseA
GO
CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
GO
INSERT INTO TestTable VALUES (1, 'Hello World')
GO
2. On the ComputerB\InstanceB instance, run the following statement to
create a database and a table:
CREATE DATABASE DatabaseB
GO
USE DatabaseB
GO
CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
GO
3. On the ComputerA\InstanceA instance, create a linked server that links to
the ComputerB\InstanceB instance. Assume the name of the linked server is
LNK_ServerB.
4. On the ComputerB\InstanceB instance, create a linked server that links to
the ComputerA\InstanceA instance. Assume the name of the linked server is
LNK_ServerA.
5. On the ComputerB\InstanceB instance, run the following statement:
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
END
GO
6. On the ComputerA\InstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXEC LNK_ServerB.DatabaseB.dbo.InsertA
GO
Then I receive the first error message.
======= My resolution that generates the second error message
======= 1. On the ComputerB\InstanceB instance, run the following statement:
sp_addserver 'ComputerA\InstanceA'
GO
sp_serveroption 'ComputerA\InstanceA', 'Data Access', 'TRUE'
GO
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * FROM [ComputerA\InstanceA].DatabaseA.dbo.TestTable
END
GO
2. On the ComputerA\InstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXECUTE [ComputerB\InstanceB].[DatabaseB].[dbo].[InsertA]
GO
Then I receive the second error message.Hi
Have you checked the security tab on the linked server properties in SSMS?
You may be using "be made without using a security context"
John
"Felix_Jiang" wrote:
> I receive the following error message when I run a distributed query against
> a loopback linked server in SQL Server 2005:
> "The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled
> the distributed transaction."
> To resolve this problem, I was told that running a distributed query against
> a loopback linked server is not supported in SQL Server 2005. And I am
> suggested to use a remote server definition (sp_addserver) instead of a
> linked server definition to resolve this problem. (Although this is only a
> temporary resolution, which will deprecate in Katmai)
> However, I run into another problem when I use the remote server definition.
> I receive the following error message:
> "Msg 18483, Level 14, State 1, Line 1
> Could not connect to server 'ServerName\SQL2005' because '' is not defined
> as a remote login at the server. Verify that you have specified the correct
> login name."
> Could anyone please help me out?
> (I include the reproduce steps for the first error message, followed by my
> resolution that generates the second error message)
> ======> Reproduce steps for the first error message
> ======> 1. On the ComputerA\InstanceA instance, run the following statement to
> create a database and a table:
> CREATE DATABASE DatabaseA
> GO
> USE DatabaseA
> GO
> CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
> GO
> INSERT INTO TestTable VALUES (1, 'Hello World')
> GO
> 2. On the ComputerB\InstanceB instance, run the following statement to
> create a database and a table:
> CREATE DATABASE DatabaseB
> GO
> USE DatabaseB
> GO
> CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
> GO
> 3. On the ComputerA\InstanceA instance, create a linked server that links to
> the ComputerB\InstanceB instance. Assume the name of the linked server is
> LNK_ServerB.
> 4. On the ComputerB\InstanceB instance, create a linked server that links to
> the ComputerA\InstanceA instance. Assume the name of the linked server is
> LNK_ServerA.
> 5. On the ComputerB\InstanceB instance, run the following statement:
> USE DatabaseB
> GO
> CREATE PROCEDURE InsertA AS
> BEGIN
> SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
> END
> GO
> 6. On the ComputerA\InstanceA instance, run the following statement:
> USE DatabaseA
> GO
> INSERT INTO TestTable
> EXEC LNK_ServerB.DatabaseB.dbo.InsertA
> GO
> Then I receive the first error message.
> =======> My resolution that generates the second error message
> =======> 1. On the ComputerB\InstanceB instance, run the following statement:
> sp_addserver 'ComputerA\InstanceA'
> GO
> sp_serveroption 'ComputerA\InstanceA', 'Data Access', 'TRUE'
> GO
> USE DatabaseB
> GO
> CREATE PROCEDURE InsertA AS
> BEGIN
> SELECT * FROM [ComputerA\InstanceA].DatabaseA.dbo.TestTable
> END
> GO
> 2. On the ComputerA\InstanceA instance, run the following statement:
> USE DatabaseA
> GO
> INSERT INTO TestTable
> EXECUTE [ComputerB\InstanceB].[DatabaseB].[dbo].[InsertA]
> GO
> Then I receive the second error message.|||John, thanks so much for your reply.
Yes, I use my Windows user logon in the security context. Additionally, both
servers are logged on using the Administrator account.
If I would like to use the remote server definition, could you please shed
any light on the second error message? Thanks!
"John Bell" wrote:
> Hi
> Have you checked the security tab on the linked server properties in SSMS?
> You may be using "be made without using a security context"
> John
> "Felix_Jiang" wrote:
> > I receive the following error message when I run a distributed query against
> > a loopback linked server in SQL Server 2005:
> > "The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled
> > the distributed transaction."
> >
> > To resolve this problem, I was told that running a distributed query against
> > a loopback linked server is not supported in SQL Server 2005. And I am
> > suggested to use a remote server definition (sp_addserver) instead of a
> > linked server definition to resolve this problem. (Although this is only a
> > temporary resolution, which will deprecate in Katmai)
> >
> > However, I run into another problem when I use the remote server definition.
> > I receive the following error message:
> > "Msg 18483, Level 14, State 1, Line 1
> > Could not connect to server 'ServerName\SQL2005' because '' is not defined
> > as a remote login at the server. Verify that you have specified the correct
> > login name."
> >
> > Could anyone please help me out?
> > (I include the reproduce steps for the first error message, followed by my
> > resolution that generates the second error message)
> >
> > ======> > Reproduce steps for the first error message
> > ======> >
> > 1. On the ComputerA\InstanceA instance, run the following statement to
> > create a database and a table:
> > CREATE DATABASE DatabaseA
> > GO
> > USE DatabaseA
> > GO
> > CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
> > GO
> > INSERT INTO TestTable VALUES (1, 'Hello World')
> > GO
> >
> > 2. On the ComputerB\InstanceB instance, run the following statement to
> > create a database and a table:
> > CREATE DATABASE DatabaseB
> > GO
> > USE DatabaseB
> > GO
> > CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
> > GO
> >
> > 3. On the ComputerA\InstanceA instance, create a linked server that links to
> > the ComputerB\InstanceB instance. Assume the name of the linked server is
> > LNK_ServerB.
> >
> > 4. On the ComputerB\InstanceB instance, create a linked server that links to
> > the ComputerA\InstanceA instance. Assume the name of the linked server is
> > LNK_ServerA.
> >
> > 5. On the ComputerB\InstanceB instance, run the following statement:
> > USE DatabaseB
> > GO
> > CREATE PROCEDURE InsertA AS
> > BEGIN
> > SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
> > END
> > GO
> >
> > 6. On the ComputerA\InstanceA instance, run the following statement:
> > USE DatabaseA
> > GO
> > INSERT INTO TestTable
> > EXEC LNK_ServerB.DatabaseB.dbo.InsertA
> > GO
> >
> > Then I receive the first error message.
> >
> > =======> > My resolution that generates the second error message
> > =======> > 1. On the ComputerB\InstanceB instance, run the following statement:
> > sp_addserver 'ComputerA\InstanceA'
> > GO
> > sp_serveroption 'ComputerA\InstanceA', 'Data Access', 'TRUE'
> > GO
> > USE DatabaseB
> > GO
> > CREATE PROCEDURE InsertA AS
> > BEGIN
> > SELECT * FROM [ComputerA\InstanceA].DatabaseA.dbo.TestTable
> > END
> > GO
> >
> > 2. On the ComputerA\InstanceA instance, run the following statement:
> > USE DatabaseA
> > GO
> > INSERT INTO TestTable
> > EXECUTE [ComputerB\InstanceB].[DatabaseB].[dbo].[InsertA]
> > GO
> >
> > Then I receive the second error message.|||Hi
For the first try I would not expect the error you have, from BOL:
Loopback linked servers cannot be used in a distributed transaction.
Attempting a distributed query against a loopback linked server from within a
distributed transaction causes an error:
Msg: 3910 Level: 16 State: 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by
another session.
Therefore the error message you are getting for the loopback server is not
the one I would expect.
For the second error http://support.microsoft.com/kb/818334 indicates that
there is possible wrong with the server names. I assume that both servers are
in the same domains?
Try using
INSERT INTO TestTable
SELECT * FROM OPENQUERY(LNK_ServerB,'EXEC DatabaseB.dbo.InsertA')
GO
John
"Felix_Jiang" wrote:
> John, thanks so much for your reply.
> Yes, I use my Windows user logon in the security context. Additionally, both
> servers are logged on using the Administrator account.
> If I would like to use the remote server definition, could you please shed
> any light on the second error message? Thanks!
> "John Bell" wrote:
> > Hi
> >
> > Have you checked the security tab on the linked server properties in SSMS?
> >
> > You may be using "be made without using a security context"
> >
> > John
> >
> > "Felix_Jiang" wrote:
> >
> > > I receive the following error message when I run a distributed query against
> > > a loopback linked server in SQL Server 2005:
> > > "The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled
> > > the distributed transaction."
> > >
> > > To resolve this problem, I was told that running a distributed query against
> > > a loopback linked server is not supported in SQL Server 2005. And I am
> > > suggested to use a remote server definition (sp_addserver) instead of a
> > > linked server definition to resolve this problem. (Although this is only a
> > > temporary resolution, which will deprecate in Katmai)
> > >
> > > However, I run into another problem when I use the remote server definition.
> > > I receive the following error message:
> > > "Msg 18483, Level 14, State 1, Line 1
> > > Could not connect to server 'ServerName\SQL2005' because '' is not defined
> > > as a remote login at the server. Verify that you have specified the correct
> > > login name."
> > >
> > > Could anyone please help me out?
> > > (I include the reproduce steps for the first error message, followed by my
> > > resolution that generates the second error message)
> > >
> > > ======> > > Reproduce steps for the first error message
> > > ======> > >
> > > 1. On the ComputerA\InstanceA instance, run the following statement to
> > > create a database and a table:
> > > CREATE DATABASE DatabaseA
> > > GO
> > > USE DatabaseA
> > > GO
> > > CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
> > > GO
> > > INSERT INTO TestTable VALUES (1, 'Hello World')
> > > GO
> > >
> > > 2. On the ComputerB\InstanceB instance, run the following statement to
> > > create a database and a table:
> > > CREATE DATABASE DatabaseB
> > > GO
> > > USE DatabaseB
> > > GO
> > > CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
> > > GO
> > >
> > > 3. On the ComputerA\InstanceA instance, create a linked server that links to
> > > the ComputerB\InstanceB instance. Assume the name of the linked server is
> > > LNK_ServerB.
> > >
> > > 4. On the ComputerB\InstanceB instance, create a linked server that links to
> > > the ComputerA\InstanceA instance. Assume the name of the linked server is
> > > LNK_ServerA.
> > >
> > > 5. On the ComputerB\InstanceB instance, run the following statement:
> > > USE DatabaseB
> > > GO
> > > CREATE PROCEDURE InsertA AS
> > > BEGIN
> > > SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
> > > END
> > > GO
> > >
> > > 6. On the ComputerA\InstanceA instance, run the following statement:
> > > USE DatabaseA
> > > GO
> > > INSERT INTO TestTable
> > > EXEC LNK_ServerB.DatabaseB.dbo.InsertA
> > > GO
> > >
> > > Then I receive the first error message.
> > >
> > > =======> > > My resolution that generates the second error message
> > > =======> > > 1. On the ComputerB\InstanceB instance, run the following statement:
> > > sp_addserver 'ComputerA\InstanceA'
> > > GO
> > > sp_serveroption 'ComputerA\InstanceA', 'Data Access', 'TRUE'
> > > GO
> > > USE DatabaseB
> > > GO
> > > CREATE PROCEDURE InsertA AS
> > > BEGIN
> > > SELECT * FROM [ComputerA\InstanceA].DatabaseA.dbo.TestTable
> > > END
> > > GO
> > >
> > > 2. On the ComputerA\InstanceA instance, run the following statement:
> > > USE DatabaseA
> > > GO
> > > INSERT INTO TestTable
> > > EXECUTE [ComputerB\InstanceB].[DatabaseB].[dbo].[InsertA]
> > > GO
> > >
> > > Then I receive the second error message.|||Hi John
Thank you for your reply. Your suggestion really works. I notice that the
reason for the second error message is that ComputerA and ComputerB are in
the same domain. When I use SQL Server Login instead to run the statement, I
didn't receive the second error message.
Many many thanks!
One remaining question, do you think the "reproduce" section qualifies a
loopback linked server?
Chen
"John Bell" wrote:
> Hi
> For the first try I would not expect the error you have, from BOL:
> Loopback linked servers cannot be used in a distributed transaction.
> Attempting a distributed query against a loopback linked server from within a
> distributed transaction causes an error:
> Msg: 3910 Level: 16 State: 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by
> another session.
> Therefore the error message you are getting for the loopback server is not
> the one I would expect.
> For the second error http://support.microsoft.com/kb/818334 indicates that
> there is possible wrong with the server names. I assume that both servers are
> in the same domains?
> Try using
> INSERT INTO TestTable
> SELECT * FROM OPENQUERY(LNK_ServerB,'EXEC DatabaseB.dbo.InsertA')
> GO
> John
> "Felix_Jiang" wrote:
> > John, thanks so much for your reply.
> >
> > Yes, I use my Windows user logon in the security context. Additionally, both
> > servers are logged on using the Administrator account.
> >
> > If I would like to use the remote server definition, could you please shed
> > any light on the second error message? Thanks!
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > Have you checked the security tab on the linked server properties in SSMS?
> > >
> > > You may be using "be made without using a security context"
> > >
> > > John
> > >
> > > "Felix_Jiang" wrote:
> > >
> > > > I receive the following error message when I run a distributed query against
> > > > a loopback linked server in SQL Server 2005:
> > > > "The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled
> > > > the distributed transaction."
> > > >
> > > > To resolve this problem, I was told that running a distributed query against
> > > > a loopback linked server is not supported in SQL Server 2005. And I am
> > > > suggested to use a remote server definition (sp_addserver) instead of a
> > > > linked server definition to resolve this problem. (Although this is only a
> > > > temporary resolution, which will deprecate in Katmai)
> > > >
> > > > However, I run into another problem when I use the remote server definition.
> > > > I receive the following error message:
> > > > "Msg 18483, Level 14, State 1, Line 1
> > > > Could not connect to server 'ServerName\SQL2005' because '' is not defined
> > > > as a remote login at the server. Verify that you have specified the correct
> > > > login name."
> > > >
> > > > Could anyone please help me out?
> > > > (I include the reproduce steps for the first error message, followed by my
> > > > resolution that generates the second error message)
> > > >
> > > > ======> > > > Reproduce steps for the first error message
> > > > ======> > > >
> > > > 1. On the ComputerA\InstanceA instance, run the following statement to
> > > > create a database and a table:
> > > > CREATE DATABASE DatabaseA
> > > > GO
> > > > USE DatabaseA
> > > > GO
> > > > CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
> > > > GO
> > > > INSERT INTO TestTable VALUES (1, 'Hello World')
> > > > GO
> > > >
> > > > 2. On the ComputerB\InstanceB instance, run the following statement to
> > > > create a database and a table:
> > > > CREATE DATABASE DatabaseB
> > > > GO
> > > > USE DatabaseB
> > > > GO
> > > > CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
> > > > GO
> > > >
> > > > 3. On the ComputerA\InstanceA instance, create a linked server that links to
> > > > the ComputerB\InstanceB instance. Assume the name of the linked server is
> > > > LNK_ServerB.
> > > >
> > > > 4. On the ComputerB\InstanceB instance, create a linked server that links to
> > > > the ComputerA\InstanceA instance. Assume the name of the linked server is
> > > > LNK_ServerA.
> > > >
> > > > 5. On the ComputerB\InstanceB instance, run the following statement:
> > > > USE DatabaseB
> > > > GO
> > > > CREATE PROCEDURE InsertA AS
> > > > BEGIN
> > > > SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
> > > > END
> > > > GO
> > > >
> > > > 6. On the ComputerA\InstanceA instance, run the following statement:
> > > > USE DatabaseA
> > > > GO
> > > > INSERT INTO TestTable
> > > > EXEC LNK_ServerB.DatabaseB.dbo.InsertA
> > > > GO
> > > >
> > > > Then I receive the first error message.
> > > >
> > > > =======> > > > My resolution that generates the second error message
> > > > =======> > > > 1. On the ComputerB\InstanceB instance, run the following statement:
> > > > sp_addserver 'ComputerA\InstanceA'
> > > > GO
> > > > sp_serveroption 'ComputerA\InstanceA', 'Data Access', 'TRUE'
> > > > GO
> > > > USE DatabaseB
> > > > GO
> > > > CREATE PROCEDURE InsertA AS
> > > > BEGIN
> > > > SELECT * FROM [ComputerA\InstanceA].DatabaseA.dbo.TestTable
> > > > END
> > > > GO
> > > >
> > > > 2. On the ComputerA\InstanceA instance, run the following statement:
> > > > USE DatabaseA
> > > > GO
> > > > INSERT INTO TestTable
> > > > EXECUTE [ComputerB\InstanceB].[DatabaseB].[dbo].[InsertA]
> > > > GO
> > > >
> > > > Then I receive the second error message.|||Hi
It would be useful to have the linked server scripted e.g.
EXEC sp_addlinkedserver
@.server='LoopBack',
@.srvproduct='',
@.provider='SQLNCLI',
@.datasrc='(LOCAL)'
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'LoopBack',@.useself=N'True',@.locallogin=NULL,@.rmtuser=NULL,@.rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @.server=N'LoopBack', @.optname=N'rpc out',
@.optvalue=N'true'
GO
CREATE DATABASE DatabaseA
GO
USE DatabaseA
GO
CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
GO
INSERT INTO TestTable VALUES (1, 'Hello World')
GO
CREATE DATABASE DatabaseB
GO
USE DatabaseB
GO
CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * from LoopBack.DatabaseA.dbo.TestTable
END
GO
EXEC InsertA
GO
USE DatabaseA
GO
INSERT INTO TestTable
EXEC LoopBack.DatabaseB.dbo.InsertA
GO
/*
Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.
*/
INSERT INTO TestTable
SELECT * FROM OPENQUERY(LoopBack,'EXEC DatabaseB.dbo.InsertA')
GO
John
"Felix_Jiang" wrote:
> Hi John
> Thank you for your reply. Your suggestion really works. I notice that the
> reason for the second error message is that ComputerA and ComputerB are in
> the same domain. When I use SQL Server Login instead to run the statement, I
> didn't receive the second error message.
> Many many thanks!
> One remaining question, do you think the "reproduce" section qualifies a
> loopback linked server?
> Chen
>
> "John Bell" wrote:
> > Hi
> >
> > For the first try I would not expect the error you have, from BOL:
> >
> > Loopback linked servers cannot be used in a distributed transaction.
> > Attempting a distributed query against a loopback linked server from within a
> > distributed transaction causes an error:
> >
> > Msg: 3910 Level: 16 State: 1
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by
> > another session.
> >
> > Therefore the error message you are getting for the loopback server is not
> > the one I would expect.
> >
> > For the second error http://support.microsoft.com/kb/818334 indicates that
> > there is possible wrong with the server names. I assume that both servers are
> > in the same domains?
> >
> > Try using
> >
> > INSERT INTO TestTable
> > SELECT * FROM OPENQUERY(LNK_ServerB,'EXEC DatabaseB.dbo.InsertA')
> > GO
> >
> > John
> >
> > "Felix_Jiang" wrote:
> >
> > > John, thanks so much for your reply.
> > >
> > > Yes, I use my Windows user logon in the security context. Additionally, both
> > > servers are logged on using the Administrator account.
> > >
> > > If I would like to use the remote server definition, could you please shed
> > > any light on the second error message? Thanks!
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > Have you checked the security tab on the linked server properties in SSMS?
> > > >
> > > > You may be using "be made without using a security context"
> > > >
> > > > John
> > > >
> > > > "Felix_Jiang" wrote:
> > > >
> > > > > I receive the following error message when I run a distributed query against
> > > > > a loopback linked server in SQL Server 2005:
> > > > > "The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled
> > > > > the distributed transaction."
> > > > >
> > > > > To resolve this problem, I was told that running a distributed query against
> > > > > a loopback linked server is not supported in SQL Server 2005. And I am
> > > > > suggested to use a remote server definition (sp_addserver) instead of a
> > > > > linked server definition to resolve this problem. (Although this is only a
> > > > > temporary resolution, which will deprecate in Katmai)
> > > > >
> > > > > However, I run into another problem when I use the remote server definition.
> > > > > I receive the following error message:
> > > > > "Msg 18483, Level 14, State 1, Line 1
> > > > > Could not connect to server 'ServerName\SQL2005' because '' is not defined
> > > > > as a remote login at the server. Verify that you have specified the correct
> > > > > login name."
> > > > >
> > > > > Could anyone please help me out?
> > > > > (I include the reproduce steps for the first error message, followed by my
> > > > > resolution that generates the second error message)
> > > > >
> > > > > ======> > > > > Reproduce steps for the first error message
> > > > > ======> > > > >
> > > > > 1. On the ComputerA\InstanceA instance, run the following statement to
> > > > > create a database and a table:
> > > > > CREATE DATABASE DatabaseA
> > > > > GO
> > > > > USE DatabaseA
> > > > > GO
> > > > > CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
> > > > > GO
> > > > > INSERT INTO TestTable VALUES (1, 'Hello World')
> > > > > GO
> > > > >
> > > > > 2. On the ComputerB\InstanceB instance, run the following statement to
> > > > > create a database and a table:
> > > > > CREATE DATABASE DatabaseB
> > > > > GO
> > > > > USE DatabaseB
> > > > > GO
> > > > > CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
> > > > > GO
> > > > >
> > > > > 3. On the ComputerA\InstanceA instance, create a linked server that links to
> > > > > the ComputerB\InstanceB instance. Assume the name of the linked server is
> > > > > LNK_ServerB.
> > > > >
> > > > > 4. On the ComputerB\InstanceB instance, create a linked server that links to
> > > > > the ComputerA\InstanceA instance. Assume the name of the linked server is
> > > > > LNK_ServerA.
> > > > >
> > > > > 5. On the ComputerB\InstanceB instance, run the following statement:
> > > > > USE DatabaseB
> > > > > GO
> > > > > CREATE PROCEDURE InsertA AS
> > > > > BEGIN
> > > > > SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
> > > > > END
> > > > > GO
> > > > >
> > > > > 6. On the ComputerA\InstanceA instance, run the following statement:
> > > > > USE DatabaseA
> > > > > GO
> > > > > INSERT INTO TestTable
> > > > > EXEC LNK_ServerB.DatabaseB.dbo.InsertA
> > > > > GO
> > > > >
> > > > > Then I receive the first error message.
> > > > >
> > > > > =======> > > > > My resolution that generates the second error message
> > > > > =======> > > > > 1. On the ComputerB\InstanceB instance, run the following statement:
> > > > > sp_addserver 'ComputerA\InstanceA'
> > > > > GO
> > > > > sp_serveroption 'ComputerA\InstanceA', 'Data Access', 'TRUE'
> > > > > GO
> > > > > USE DatabaseB
> > > > > GO
> > > > > CREATE PROCEDURE InsertA AS
> > > > > BEGIN
> > > > > SELECT * FROM [ComputerA\InstanceA].DatabaseA.dbo.TestTable
> > > > > END
> > > > > GO
> > > > >
> > > > > 2. On the ComputerA\InstanceA instance, run the following statement:
> > > > > USE DatabaseA
> > > > > GO
> > > > > INSERT INTO TestTable
> > > > > EXECUTE [ComputerB\InstanceB].[DatabaseB].[dbo].[InsertA]
> > > > > GO
> > > > >
> > > > > Then I receive the second error message.sql

Distributed query & OLE DB provider

Hi,

I hope I'm in correct forum..

I'm working on simple OLE DB provider that should be usable in SQL server as a linked server. The provider should provide some support for SQL (certain SELECTs for now) and therefore I'd like the SQL server to delegate portions of query processing to the provider instead of fetching all rows and doing SELECT on its own.

Alas, I'm no OLE DB expert and my provider apparently doesn't implement enough things to actually make SQL server pass the query to it. My provider's session currently implements mandatory interfaces + couple of others (IDBCreateCommand, IDBSchemaRowset) and via provider properties, I 'claim' to have full support for SQL92. I'm testing the provider with SQL Server 2005 ('Express edition').

I'd like to know what am I missing in the implementation, or whether/how can I find out what makes SQL server decide between fetching full rowset and creating a command to let rthe query run in provider.

Thanks,

L.

In general, if you use 4-part name query, the processing is done at the local server. That means the remote data is pulled to the local server before work is done. However, if openquery/openrowset/opendatasource() is used the remote processing is done on the remote server. This is what we often call as pass-through query.

If you're creating a custom oledb provider, what would be more important is the distributed transaction implementation. I suggest you check msdn on this.

|||

I just begun reading on the transactions yesterday evening, so I certainly shall dig into it bit deeper as you suggest, thanks .

Regarding 4-part name query, you wrote 'in general'. Is the processing 'always' done locally, or is there a way to make even 4-part name queries run remotely?

|||

If you have a remote filter and your provider is delegateable (see http://msdn2.microsoft.com/en-us/library/ms180972.aspx) only the filtered rows are returned.

e.g.

Code Snippet

select a.name,b.name
from master..sysdatabases a join remotesrv.master.dbo.sysdatabases b on a.status=b.status
where b.name like 'D%'

What going on here is the local engine delegates the following work to the remote server

Code Snippet

select name from master.dbo.sysdatabases

where name like 'D%'

instead of asking the remote server to return the entire table then do join and filter locally.

Note that the join is still done locally!

|||

Well, according to description available at the link, my provider 'should be' delegateable as it claims to support SQL 92 and supports the command. I suppose there's nothing like a tool to tell me 'yes' or 'no, you fail here and here to be SQL command provider'..?

However, I guess I might have a problem with collation rules as I haven't cared about them at all. I'll check it later today.

Thanks for help!

Distributed Query

I would like to perform Distributed Queries on Active Directory via views in
SQL Server. I have created a linked server by executing:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'
However whenever I run even the simplest query:
SELECT *FROM OPENQUERY( ADSI,
'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
I get the following error:
OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
that the user did not have the permission to perform the operation.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
returned 0x80040e09: The provider indicates that the user did not have the
permission to perform the operation.].
I am logged into the server as an Administrator and logging into query
analyzer as sa - so I should have all required permissions.
Any ideas? Any help or direction is really appreciated - Thanks in advance,
J. Shane Kunkle
jkunkle@.vt.edu
What account is SQL Server running under? The query is run in it's context.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>I would like to perform Distributed Queries on Active Directory via views
>in SQL Server. I have created a linked server by executing:
> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
> 'ADSDSOObject', 'adsdatasource'
> However whenever I run even the simplest query:
> SELECT *FROM OPENQUERY( ADSI,
> 'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
> I get the following error:
> OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
> that the user did not have the permission to perform the operation.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> ICommandPrepare::Prepare returned 0x80040e09: The provider indicates that
> the user did not have the permission to perform the operation.].
> I am logged into the server as an Administrator and logging into query
> analyzer as sa - so I should have all required permissions.
> Any ideas? Any help or direction is really appreciated - Thanks in
> advance,
> J. Shane Kunkle
> jkunkle@.vt.edu
>
|||Thanks for the response - I think we've gotten somewhere - under properties
of the linked server (under the security tab) i set all logins to use an
Administrator account (for Active Directory).
Now when i run the same basic query i get the following error:
Could not execute query against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute
returned 0x80040e22].
Unfortunately it's not very informative but I'm guessing that it means that
there is a problem with my query (syntax, LDAP string, etc)? I have tried
changing around the query a bit but no success yet.
Any ideas? Am I headed in the wrong direction? Thanks again for the help!
J. Shane Kunkle
jkunkle@.vt.edu
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uEYPMEAXFHA.1468@.tk2msftngp13.phx.gbl...
> What account is SQL Server running under? The query is run in it's
> context.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
> news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>

Distributed Query

I would like to perform Distributed Queries on Active Directory via views in
SQL Server. I have created a linked server by executing:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'
However whenever I run even the simplest query:
SELECT *FROM OPENQUERY( ADSI,
'SELECT SN FROM ''LDAP:// OU=myOu,DC=subdomain,DC=mydomain,DC=com'
' ')
I get the following error:
OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
that the user did not have the permission to perform the operation.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prep
are
returned 0x80040e09: The provider indicates that the user did not have the
permission to perform the operation.].
I am logged into the server as an Administrator and logging into query
analyzer as sa - so I should have all required permissions.
Any ideas? Any help or direction is really appreciated - Thanks in advance,
J. Shane Kunkle
jkunkle@.vt.eduWhat account is SQL Server running under? The query is run in it's context.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>I would like to perform Distributed Queries on Active Directory via views
>in SQL Server. I have created a linked server by executing:
> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
> 'ADSDSOObject', 'adsdatasource'
> However whenever I run even the simplest query:
> SELECT *FROM OPENQUERY( ADSI,
> 'SELECT SN FROM ''LDAP:// OU=myOu,DC=subdomain,DC=mydomain,DC=com'
' ')
> I get the following error:
> OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
> that the user did not have the permission to perform the operation.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> ICommandPrepare::Prepare returned 0x80040e09: The provider indicates that
> the user did not have the permission to perform the operation.].
> I am logged into the server as an Administrator and logging into query
> analyzer as sa - so I should have all required permissions.
> Any ideas? Any help or direction is really appreciated - Thanks in
> advance,
> J. Shane Kunkle
> jkunkle@.vt.edu
>|||Thanks for the response - I think we've gotten somewhere - under properties
of the linked server (under the security tab) i set all logins to use an
Administrator account (for Active Directory).
Now when i run the same basic query i get the following error:
Could not execute query against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute
returned 0x80040e22].
Unfortunately it's not very informative but I'm guessing that it means that
there is a problem with my query (syntax, LDAP string, etc)? I have tried
changing around the query a bit but no success yet.
Any ideas? Am I headed in the wrong direction? Thanks again for the help!
J. Shane Kunkle
jkunkle@.vt.edu
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uEYPMEAXFHA.1468@.tk2msftngp13.phx.gbl...
> What account is SQL Server running under? The query is run in it's
> context.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
> news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>

Distributed Query

I would like to perform Distributed Queries on Active Directory via views in
SQL Server. I have created a linked server by executing:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'
However whenever I run even the simplest query:
SELECT *FROM OPENQUERY( ADSI,
'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
I get the following error:
OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
that the user did not have the permission to perform the operation.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
returned 0x80040e09: The provider indicates that the user did not have the
permission to perform the operation.].
I am logged into the server as an Administrator and logging into query
analyzer as sa - so I should have all required permissions.
Any ideas? Any help or direction is really appreciated - Thanks in advance,
J. Shane Kunkle
jkunkle@.vt.eduWhat account is SQL Server running under? The query is run in it's context.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>I would like to perform Distributed Queries on Active Directory via views
>in SQL Server. I have created a linked server by executing:
> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
> 'ADSDSOObject', 'adsdatasource'
> However whenever I run even the simplest query:
> SELECT *FROM OPENQUERY( ADSI,
> 'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
> I get the following error:
> OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
> that the user did not have the permission to perform the operation.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> ICommandPrepare::Prepare returned 0x80040e09: The provider indicates that
> the user did not have the permission to perform the operation.].
> I am logged into the server as an Administrator and logging into query
> analyzer as sa - so I should have all required permissions.
> Any ideas? Any help or direction is really appreciated - Thanks in
> advance,
> J. Shane Kunkle
> jkunkle@.vt.edu
>|||Thanks for the response - I think we've gotten somewhere - under properties
of the linked server (under the security tab) i set all logins to use an
Administrator account (for Active Directory).
Now when i run the same basic query i get the following error:
Could not execute query against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandText::Execute
returned 0x80040e22].
Unfortunately it's not very informative but I'm guessing that it means that
there is a problem with my query (syntax, LDAP string, etc)? I have tried
changing around the query a bit but no success yet.
Any ideas? Am I headed in the wrong direction? Thanks again for the help!
J. Shane Kunkle
jkunkle@.vt.edu
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uEYPMEAXFHA.1468@.tk2msftngp13.phx.gbl...
> What account is SQL Server running under? The query is run in it's
> context.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "J. Shane Kunkle" <shane@.caudillweb.com> wrote in message
> news:%232G4zX%23WFHA.3532@.TK2MSFTNGP10.phx.gbl...
>>I would like to perform Distributed Queries on Active Directory via views
>>in SQL Server. I have created a linked server by executing:
>> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
>> 'ADSDSOObject', 'adsdatasource'
>> However whenever I run even the simplest query:
>> SELECT *FROM OPENQUERY( ADSI,
>> 'SELECT SN FROM ''LDAP://OU=myOu,DC=subdomain,DC=mydomain,DC=com'' ')
>> I get the following error:
>> OLE DB provider 'ADSDSOObject' reported an error. The provider indicates
>> that the user did not have the permission to perform the operation.
>> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
>> ICommandPrepare::Prepare returned 0x80040e09: The provider indicates
>> that the user did not have the permission to perform the operation.].
>> I am logged into the server as an Administrator and logging into query
>> analyzer as sa - so I should have all required permissions.
>> Any ideas? Any help or direction is really appreciated - Thanks in
>> advance,
>> J. Shane Kunkle
>> jkunkle@.vt.edu
>>
>