<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 Security
»
"Login failed for user '(null)'. Reason: Not...
36 posts, Page 1 of 4
1
2
3
4
»
»»
"Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server conn "
Rate Topic
Display Mode
Topic Options
Author
Message
Jani Lane
Jani Lane
Posted Tuesday, March 18, 2008 1:55 PM
Grasshopper
Group: General Forum Members
Last Login: Thursday, May 02, 2013 3:19 PM
Points: 14,
Visits: 47
Good day!
Don't know much about security, but I've created my web service function to return a dataset from SQL Server 2000. When I invoked the function it is giving me this error in the page although I can generate a dataset and connect to the db, only when invoking the web service that I'm getting this.
Need help please.
Thanks
Post #471172
george sibbald
george sibbald
Posted Tuesday, March 18, 2008 2:19 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 5,314,
Visits: 11,303
usual reason for this error is the sql instance is set to only accept windows authenticated logins but you are trying to connect with a sql authenticated ID. check the properties on your sql instance.
If this is your problem will need to change authentication mode to windows and sql authentication, which will require a sql bounce to take effect.
...or connect with a windows id, which is more secure
---------------------------------------------------------------------
Post #471183
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Tuesday, March 18, 2008 2:49 PM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 5:09 AM
Points: 31,526,
Visits: 13,864
When you connect, you use your credentials, likely your Windows account. When the web service connects, it's probably using different credentials, probably IIS credentials. Be sure those have access to the SQL server.
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
Post #471205
Marios Philippopoulos
Marios Philippopoulos
Posted Saturday, March 22, 2008 9:13 AM
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 2:25 PM
Points: 1,825,
Visits: 3,484
I'm having the exact same issue when using a linked server on a SQL Server 2005 instance (target is a SQL 2000 instance).
The exact error is:
OLE DB provider "SQLNCLI" for linked server "server\instance" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
The user getting this error is able to successfully connect to the target instance on SSMS, while logged on the source sql 2005 instance where the linked server is created.
The linked server is created by the following command (note that security is configured for windows integrated auth., NOT SQL auth.):
EXEC sp_addlinkedserver 'server\instance2k', N'SQL Server';
The user is getting the error above after running:
select * from [server\instance2k].DBNAME.dbo.TableName;
I find this issue odd because, as I mentioned, the user is able to connect with no problems to
server\instance2k
on SSMS, while logged on the sql2005 instance.
I strongly suspect this is the exact same problem as that posted initially on this thread.
__________________________________________________________________________________
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #473224
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Saturday, March 22, 2008 10:58 AM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 5:09 AM
Points: 31,526,
Visits: 13,864
You need to check the linked server credentials to see what's there. You can either have set credentials for everyone, rights for some people and not others, or pass through credentials.
Check in SSMS how the linked server security is configured.
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
Post #473239
Marios Philippopoulos
Marios Philippopoulos
Posted Saturday, March 22, 2008 1:03 PM
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 2:25 PM
Points: 1,825,
Visits: 3,484
Connections are made using the login's current security context.
So in the following query, the login running the query is the same account in whose context the connection is attempted:
SELECT * from [server\instance2k].dbName.dbo.tblName;
The puzzling thing is that the same user is able to connect to instance server\instance2k through SSMS, while logged on the instance hosting the linked server.
__________________________________________________________________________________
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #473245
RBarryYoung
RBarryYoung
Posted Saturday, March 22, 2008 2:40 PM
SSCrazy Eights
Group: General Forum Members
Last Login: Wednesday, June 12, 2013 11:17 AM
Points: 9,855,
Visits: 9,376
Marios Philippopoulos (3/22/2008)
Connections are made using the login's current security context.
So in the following query, the login running the query is the same account in whose context the connection is attempted:
SELECT * from [server\instance2k].dbName.dbo.tblName;
The puzzling thing is that the same user is able to connect to instance server\instance2k through SSMS, while logged on the instance hosting the linked server.
In your case it is pretty clear what the problem is (not so clear for the OP, who had less detail). What you are running into is almost certainly the "Two Hop Rule", which is that under windows domain security, an impersonated security context cannot re-impersonate (that is, it cannot generate the same impersonation on another server).
This is relevant because when you connect from a client to the SQL Server, it impersonates you to generate the security context. In order to get to the linked server using Trusted connections, the security context of your server session would have to be re-impersonated on the target server, and that is not allowed. So even though you can connect directly from your client to both servers, you cannot connect from you client to the first server and then through that to the linked server, because that would be a two-hop impersonation.
The way to test to see if this is really the problem is to find a way to get a session on your SQL server without it having to be impersonated and then try to connect to the linked server from there. I know of two ways to do this:
1) Log on to your server at the console or through Remote Desktop, then run your client to connect to the SQL server on the same box; then connect through it to the Linked Server; it should work now. OR..
2) Write a stored procedure that tries to connect to the linked server and run it using the SQL Agent making sure the the Run As.. is set. (Actually, I am not sure that this still works under Sql2005..)
If you ask Microsoft, they will say that the solution to this problem is Kerberos, but I have yet to see anyone successfully use Kerberos to address this problem in a complex multi-domain corporate enterprise network.
The solution that everyone ends up using is SQL Logins for server-to-server communications. Not ideal, and not a secure as anyone would like, but it does work.
-- RBarryYoung
,
(302)375-0451
blog:
MovingSQL.com
, Twitter:
@RBarryYoung
Proactive
Performance Solutions, Inc.
"Performance is our middle name."
Post #473248
Marios Philippopoulos
Marios Philippopoulos
Posted Monday, March 24, 2008 8:31 AM
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 2:25 PM
Points: 1,825,
Visits: 3,484
It is indeed as you say.
When running the same SELECT linked-server query while RDP'd directly on the source server (the server instance to which the linked server is added) it works! The error occurs when running the query while connected remotely to the server (through SSMS).
So it looks like it is indeed a double-hop integrated authentication issue.
We are running this query from a SQL job so we just need to give the SQL Agent account sufficient privileges on the target instance for it to be able to view the data.
__________________________________________________________________________________
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #473489
RBarryYoung
RBarryYoung
Posted Monday, March 24, 2008 9:14 AM
SSCrazy Eights
Group: General Forum Members
Last Login: Wednesday, June 12, 2013 11:17 AM
Points: 9,855,
Visits: 9,376
Marios: Glad I could help. I lost many days to this problem the first time that I tripped over it.
-- RBarryYoung
,
(302)375-0451
blog:
MovingSQL.com
, Twitter:
@RBarryYoung
Proactive
Performance Solutions, Inc.
"Performance is our middle name."
Post #473520
Marios Philippopoulos
Marios Philippopoulos
Posted Monday, March 24, 2008 11:41 AM
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 2:25 PM
Points: 1,825,
Visits: 3,484
rbarryyoung (3/24/2008)
Marios: Glad I could help. I lost many days to this problem the first time that I tripped over it.
Thank you!
This problem rears its ugly head in many different ways. It occurs when I attempt to open an SSRS report on my local workstation that connects through Windows auth. to a production instance and surveys user-database permissions. To resolve I need to create a special SQL login with sysadmin permissions on the instance! The alternative would be to map that login to EVERY single user database with
db_datareader
permissions, something hard to pull off logistically (and messy).
So at the moment I'm stuck with a less than ideal scenario, and I want to try the Kerberos-auth option...
__________________________________________________________________________________
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #473616
« Prev Topic
|
Next Topic »
36 posts, Page 1 of 4
1
2
3
4
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
