Linked Server - name not getting passed over?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

M Bourgon - 21 Aug 2008 22:22 GMT

I am trying to connect from ServerA to ServerB using linked server.
Below is the setup script, but it's the basic linked server (RPC is
on, Data Access is on, use login's permissions).

Doing "test connection" works, and if I open catalogs I see all the
databases listed. If I run an open query "select * from openquery
(ServerB, 'select @@version')" it works.

However, if I try to run an SP (that requires a parameter) which
exists on the server, I get:

Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'FTW-PR-REPL-01' because '' is not defined
as a remote login at the server. Verify that you have specified the
correct login name. .

(note that the ". ." is actually in the error string, and the '' is
blank - even though I'm connected via windows authentication in
SSMS.)

I have even added a security group that I'm in as a user on both
machines. No dice.

Both are sql 2005, 9.00.3042.

One idea is that it's something wrong with the SP - its job is to
create a table, and it has to use Dynamic SQL to do so. I can't
create one via a linked server, and so I've resorted to creating an
SP, then calling it via the linked server.

This also fails:
SELECT * FROM OPENQUERY(ServerB, 'exec db_tools.dbo.SampleCode
''20080201''')

Here's the Linked Server script:
/****** Object: LinkedServer [ServerB] Script Date: 08/21/2008
16:11:25 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'ServerB',
@srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is
changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'ServerB',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO
EXEC master.dbo.sp_serveroption @server=N'ServerB',
@optname=N'collation compatible', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'data
access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'dist',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'pub',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'rpc',
@optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'rpc
out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'sub',
@optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'connect
timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ServerB',
@optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'lazy
schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'query
timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'use
remote collation', @optvalue=N'true'

Reply to this Message

Erland Sommarskog - 21 Aug 2008 23:22 GMT

> I am trying to connect from ServerA to ServerB using linked server.
> Below is the setup script, but it's the basic linked server (RPC is
[quoted text clipped - 11 lines]
> as a remote login at the server. Verify that you have specified the
> correct login name. .

Real stupid question is ServerB and FTW-PR-REPL-01 the same server?
That is, it is not the case the stored procedure tries to access
a third linked server?

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

M Bourgon - 22 Aug 2008 14:40 GMT

> That is, it is not the case the stored procedure tries to access
> a third linked server?

No, no third server. Just was trying to anonymize it and missed
that.
How about this:
Could not connect to server 'ServerB' because '' is not defined
as a remote login at the server. Verify that you have specified the
correct login name. .

And, just to add more info - I have a linked server setup from ServerB
to ServerA, and I'm getting the same problem trying to run a similar
dynamic SQL SP. Same message.

Reply to this Message

Erland Sommarskog - 22 Aug 2008 22:58 GMT

> No, no third server. Just was trying to anonymize it and missed
> that.
[quoted text clipped - 6 lines]
> to ServerA, and I'm getting the same problem trying to run a similar
> dynamic SQL SP. Same message.

OK, thanks for the clarification. It is certainly puzzling that it
works with one query, if only to get @@version.

But I see that you have:

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ServerB',
@useself=N'True',
@locallogin=NULL,
@rmtuser=NULL,
@rmtpassword=NULL

I often find when I use linked servers that I need to set up a login,
and the @useself='true' does not work well.

But I think I have an idea of what is going on. Who are you logged in
as? An SQL Server login or a Windows login? Is this login present on
ServerB? And furhermore, does this login map to a user in the database
you are trying to access?

Signature

Reply to this Message

M Bourgon - 22 Aug 2008 23:46 GMT

> OK, thanks for the clarification. It is certainly puzzling that it
> works with one query, if only to get @@version.
[quoted text clipped - 14 lines]
> ServerB? And furhermore, does this login map to a user in the database
> you are trying to access?

I'm logged in as me (Windows login), and I'm part of a Windows group,
added to both servers and set up as "sysadmin" . However, I am a
domain admin, which muddies things. Also odd: my Windows security
group name doesn't show up in the drop-down when trying to add a
"Local Login", and when I try to explicitly add the group, I get
"<name of the group> is not a valid login or you do not have
permission".

Reply to this Message

M Bourgon - 22 Aug 2008 23:47 GMT

(forgot to answer this one)

> And furthermore, does this login map to a user in the database
> you are trying to access?

No, it doesn't - I assumed sysadmin would give me rights.

Reply to this Message

Erland Sommarskog - 23 Aug 2008 10:36 GMT

> (forgot to answer this one)
>> And furthermore, does this login map to a user in the database
>> you are trying to access?
>
> No, it doesn't - I assumed sysadmin would give me rights.

Yes, if you connect to the server with CONTROL permission on server
level, you map to dbo. It seems that this is somehow not happening here.

Try this:

SELECT * FROM OPENQUERY(ServerB,
'SELECT * FROM fn_my_permissions(NULL, NULL)')

What do you see?

Signature

Reply to this Message

M Bourgon - 25 Aug 2008 14:45 GMT

> > (forgot to answer this one)
> >> And furthermore, does this login map to a user in the database
[quoted text clipped - 11 lines]
>
> What do you see?

entity_name    subentity_name    permission_name
server        CONNECT SQL
server        SHUTDOWN
server        CREATE ENDPOINT
server        CREATE ANY DATABASE
server        ALTER ANY LOGIN
server        ALTER ANY CREDENTIAL
server        ALTER ANY ENDPOINT
server        ALTER ANY LINKED SERVER
server        ALTER ANY CONNECTION
server        ALTER ANY DATABASE
server        ALTER RESOURCES
server        ALTER SETTINGS
server        ALTER TRACE
server        ADMINISTER BULK OPERATIONS
server        AUTHENTICATE SERVER
server        EXTERNAL ACCESS ASSEMBLY
server        VIEW ANY DATABASE
server        VIEW ANY DEFINITION
server        VIEW SERVER STATE
server        CREATE DDL EVENT NOTIFICATION
server        CREATE TRACE EVENT NOTIFICATION
server        ALTER ANY EVENT NOTIFICATION
server        ALTER SERVER STATE
server        UNSAFE ASSEMBLY
server        CONTROL SERVER

Reply to this Message

Erland Sommarskog - 25 Aug 2008 22:24 GMT

>> > (forgot to answer this one)
>> >> And furthermore, does this login map to a user in the database
[quoted text clipped - 16 lines]
>...
> server CONTROL SERVER

So you have CONTROL SERVER and thuse you should map to dbo in any database,
as far as I know. Well, I will have to admit that I'm starting to run out of
ideas.

What if you create a new database on ServerB and create some dummy procedure
in that database. Are you able to access it? My thinking is that maybe the
dbo mapping in the database you are trying to access is out of whack.

You could also try to create an explicit user for your login in the database
you are trying to access to see if that helps.

Signature

Reply to this Message

M Bourgon - 25 Aug 2008 23:19 GMT

> So you have CONTROL SERVER and thuse you should map to dbo in any database,
> as far as I know. Well, I will have to admit that I'm starting to run out of
> ideas.

Erland, that scares me - I thought you knew Everything! :)

> What if you create a new database on ServerB and create some dummy procedure
> in that database. Are you able to access it? My thinking is that maybe the
> dbo mapping in the database you are trying to access is out of whack.

(I created a database called "test", added a table called test, then
created this SP)
CREATE PROCEDURE mbtest
AS
BEGIN
SELECT * FROM test WHERE startdate > '20060421'
END

Ran the following on ServerA:
EXEC ServerB.test.dbo.mbtest

> You could also try to create an explicit user for your login in the database
> you are trying to access to see if that helps.

No luck - same error message. Made sure to give it execute
permissions, as well as doing it with both databases (my current one,
and the test one)

Here's a different question, then - I am trying to set up code to
automatically partition data out of a table. It needs to make sure
the database exists (create one for each month) - if it doesn't exist,
create it. I need to make sure the table exists - and create it if it
doesn't. I then need to populate it. Due to the size of the result
set, even narrowing it down to a day, I get a timeout if I try to run
a select on ServerB to pull data from ServerA.

How would you suggest doing this? I've been using Dynamic SQL and
SPs - and for 3 of the 4 tables it works just fine. It's that last
table that's giving me fits.

Many thanks for all the assistance.
Michael

Reply to this Message

Erland Sommarskog - 28 Aug 2008 23:31 GMT

>> So you have CONTROL SERVER and thuse you should map to dbo in any
>> database, as far as I know. Well, I will have to admit that I'm
>> starting to run out of ideas.
>
> Erland, that scares me - I thought you knew Everything! :)

Did I tell you that I hate linked servers? Always a lot of strange
problems with them.

> No luck - same error message. Made sure to give it execute
> permissions, as well as doing it with both databases (my current one,
> and the test one)

I guess you need to add a linked server login with sp_addlinkedsrvlogin
and give up on @useself = 'TRUE'

> Here's a different question, then - I am trying to set up code to
> automatically partition data out of a table. It needs to make sure
[quoted text clipped - 7 lines]
> SPs - and for 3 of the 4 tables it works just fine. It's that last
> table that's giving me fits.

You could of course increase the query timeout with sp_serveroption.

Another alternative is to take data in batches, but for this to be
meaningful you would have to batch over the clustered index, or over
a non-clustred index, where you take so small portions at a time that
you get an Index seek and not a table scane anyway.

Yet an alternative to try is the "FAST n" hint. This encourages
SQL Server to bring back some rows quickly, which should stop the
query timeout. But the overall query execution time is likely to
increase.

And of course, it may be a good idea to improve indexing on the table
you are getting data from, so that queries run faster.

Signature

Reply to this Message

M Bourgon - 29 Aug 2008 17:19 GMT

> Did I tell you that I hate linked servers? Always a lot of strange
> problems with them.

Tell me about it. This is the latest strange one, but I agree.

> I guess you need to add a linked server login with sp_addlinkedsrvlogin
> and give up on @useself = 'TRUE'

Okay - I'll try out some other login options.

> > Here's a different question, then - I am trying to set up code to
> > automatically partition data out of a table. It needs to make sure
[quoted text clipped - 14 lines]
> a non-clustred index, where you take so small portions at a time that
> you get an Index seek and not a table scane anyway.

Okay. We had gone down to 1 day's data, I guess next is one hour.

> Yet an alternative to try is the "FAST n" hint. This encourages
> SQL Server to bring back some rows quickly, which should stop the
> query timeout. But the overall query execution time is likely to
> increase.

Aha! Good idea, I'll give that a try. .

> And of course, it may be a good idea to improve indexing on the table
> you are getting data from, so that queries run faster.

The problem is that it's already got an index on the key fields, it's
just a massive amount of data - hence us trying to partition it.

Thanks again VERY MUCH for your help, Erland.

Michael

Reply to this Message