Solved

MSSQL linked server problem

Posted on 2012-03-13
6
801 Views
Last Modified: 2012-03-19
Hi Experts,

I have tried to make a db link (linked server) between two sql servers without success.
I've read several suggestions and solutions on the Net, but none of them helped us.

Please give me a solution.

The details are:

Server1 (link owner):

sql server 2008 R2

Server2 (linked):

sql server 2008

both of the server are in the same domain, use the same auth. type (NTLM)

My aim is to make the linked server WITHOUT delegation. All of the suggestion what I've read said that
we HAVE TO USE kerberos and delegation. My question is, that is it possible to make linked server without Kerberos (using only NTLM auth.) and delegation?

Current settings are:

Active Directory

server1 and server2 >> Delegation TAB:

  Trust this computer for delegation to any service (Kerberos only) [checked]

service runner user (same for both) >> Account TAB

  Account is sensitive and cannot be delegated [unchecked]
  (Remark: we've tried to checked the above mentioned option - as it's sugested - too, with the same result)


SQL settings:
We made the linked server on behalf of the service runner user (it has sysadmin role).


Making the link was possible only as SQL server with "Be made using the login's current sec. context" security option. In this case we could connect to the linked server as administrator, but only for 10 minutes (as this is the default setting for the query timeout). If we tried to use it as a "normal" db user it didn't work. We got the err. msg:

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


Anyway I have tried all the variations of linked server properties, ie.: mappings, and different connections types. Unfortunaly none of them worked. Please suggest me a solution.

Thx: Zsolt
0
Comment
Question by:HidasiZs
  • 3
  • 3
6 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
I think the issue is that the linked server will use the login of the user that actually runs the sql using the linked server, it does NOT use the sql server's "credentials"

means, if I connect to the server from my workstation with trusted connection to the box that has the linked server defined, and I run the query using the linked server, it will use my credentials (presuming that the linked server's config is to use no "mapping".

which is what you seem to have been able to confirm, but the query does run too long?
in which case your issue is that you run a query that takes too long, and not a linked server issue as such?
0
 

Author Comment

by:HidasiZs
Comment Utility
Thank you for your reply. I'm sure that the problem is not the running time of the query. I used a very simple query to test the db link, so it shouldn't need significant time. The trouble is that when I make a dblink (with the above mentioned conditions) its "lifetime" is very short (and works only for the user who created the dblink). It seems that the limit is the "Connection Timeout" and the "Query Timeout". I understand if "Connection Timeout" is 0, then it uses the value of "Query Timeout". If "Query Timeout" is 0, too, then it will use the server default setting, which is 10 minutes). What we want is to make a dblink with the same grant for all users who use db connection via the dblink. How is it possible in mssql?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
no.

connection timeout is the time to establish the connection.
query timeout is the time that the query is left running before failing with timeout
they are not interchanged, or used as default for the other "timeout".

and there is no "default" on the server, the default would be 30 (query timeout) and 60 (connection timeout)


next: the linked server is available for all users on the instance, from what I know. you cannot "hide" it.
however, when you run a query over the linked server, the permissions used to run the query on the other server are those from the one that runs the query, and not from the one that created the linked server.
except, of course, if the security mappings in the linked server specify something else ...


the error you get (login failed) plainly means that the login used to run the query is not enabled on the "other" server, or the other server failed to identify the windows login at some point.
this might point to an issue in the kerebos setup in your domain...
which is very difficult to check/validate at distance ...
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:HidasiZs
Comment Utility
Thanks a lot. This is what I was afraid of... I'm sure that the login what I used is enabled on the other server, so the only thing what can be failed is the windows login somehow. Our system administrators want to avoid the using of Kerberos (I don't know why), so probably that the problem is around this point.

My question is that is it possible to make a fairly well working  dblink without the using of Kerberos or is it required by all means?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
db link can work with SQL logins just fine ... which means that you need to use mixed authentication on the target server to enable such logins.
and the linked server to map all logins to that login (which is what I have in my configs, btw)
0
 

Author Comment

by:HidasiZs
Comment Utility
I know the mixed authentication, but our system administrators want to avoid it on security reasons. Anyway, if you - as an expert - says that this is the only solution, I'll try to make an impression on my boss. Thanks that you found time for my question.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now