Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MSSQL linked server problem

Posted on 2012-03-13
6
Medium Priority
?
836 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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37714381
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
ID: 37714602
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37715032
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:HidasiZs
ID: 37715093
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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 37724301
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
ID: 37736945
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

916 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