Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 server authentication

Author  Topic 

b11091019
Starting Member

15 Posts

Posted - 2012-01-26 : 14:29:14
I have SQL permissions (windows authentication in this case) on two servers, s1 and s2

in SSMS, I can connect to either one and run

select * from foo.dbo.bar

I'd like to be able to log into s1, e.g. and run

select * from s2.foo.dbo.bar
into foo.dbo.bar
where <some condition that I need>

however, when do that, I only get default permissions in my reference to s2. In this case, the defaults are NO permissions at all.

Is there a way to do this and say, "hey, please use my current credentials for my reference to s2"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-26 : 14:38:15
How is the linked server configured? What account it uses is specified in its config.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

b11091019
Starting Member

15 Posts

Posted - 2012-01-26 : 14:52:07
quote:
Originally posted by tkizer

How is the linked server configured? What account it uses is specified in its config.




Where can I see that?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-26 : 15:52:02
In SSMS, navigate to Server Objects, Linked Servers, and then right click on your linked server and go to properties. Now go to the security page. What do you see?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

b11091019
Starting Member

15 Posts

Posted - 2012-01-26 : 16:56:04
There is nothing in the list. So, since nothing is pre-defined, (how) can I supply credentials at run-time?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-26 : 17:01:27
How are you doing a query to this then?: select * from s2.foo.dbo.bar

You are running that query on s1, right?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

b11091019
Starting Member

15 Posts

Posted - 2012-01-26 : 17:07:18
I'm not doing that query, but I want to...from s1. I suppose I phrased the question badly. When I try I get:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

But I don't want to login anonymously, since I can login to s2 directly. I want to issue that query to s2 while logged into s1 and tell s2 to use the same credentials that got me into s1 in the first place.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-26 : 17:13:17
Aha! That part is very important. You would need to involve your Active Directory admins to make that work, believe it's called delegation. To get around this, I connect with SQL authentication when I want to query a remote server. My linked server points to the current security context.

You could alternatively use OPENQUERY.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

b11091019
Starting Member

15 Posts

Posted - 2012-01-27 : 09:35:10
Hmmm... so how do you "connect with SQL authentication when I want to query a remote server"? Do you have any examples? e.g. a query that accesses databases on more than one server.

fwiw I tried using OPENQUERY but got the same error message
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-27 : 12:30:56
You would log into s1 with a SQL account, one that has permissions to both boxes. OR, you would configure the linked server to use a SQL account (not secure!).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

b11091019
Starting Member

15 Posts

Posted - 2012-01-27 : 13:23:05
OK -- I'll try the first approach. I'm not a dba so I can't do the second.
Go to Top of Page

b11091019
Starting Member

15 Posts

Posted - 2012-01-27 : 15:21:49
OK -- seems I'm out of luck, since we don't use SQL authentication at my shop
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-27 : 15:47:10
You aren't out of luck, you just have some work to do: http://www.databasejournal.com/features/mssql/article.php/3696506/Setting-Up-Delegation-for-Linked-Servers.htm

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

b11091019
Starting Member

15 Posts

Posted - 2012-01-27 : 15:59:29
That would be nice, but I don't have access to the domain account properties window or I can't find it. I can't find Administrative Tools for my box either. I suppose my access is limited.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-27 : 16:00:51
Well you definitely will need to work with other people on this, so if that's important, get the domain admins involved.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
   

- Advertisement -