Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

"Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server conn " Expand / Collapse
Author
Message
Posted Monday, March 24, 2008 11:43 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:53 AM
Points: 1,861, Visits: 3,599
Jani Lane (3/18/2008)
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


BTW, apologies for cutting into this thread with my own problem! ;)
I think it is the same as what you posted, but, if not, pls let us know.

thx!


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
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 #473620
Posted Wednesday, March 26, 2008 12:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 2, 2013 3:19 PM
Points: 14, Visits: 47
Don't know how our DBA resolved this, but he gave me a new ID/password that I've used in my connectionString. Not a very wise move but for now that's all I can do, to include those credentials in my connection string. cheers
Post #474529
Posted Wednesday, March 26, 2008 4:46 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
rbarryyoung (3/22/2008)
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.


The answer isn't just Kerberos, as in Kerberos authentication, but actually Kerberos delegation. Kerberos authentication, by default, is just like NTLM: double-hops aren't permitted. With Kerberos delegation, multiple hops can be set up. Where we typically see this is web browser (client) to web server (first hop) to SQL Server (second hop) all trying to pass the user credentials from the web browser all the way through to the SQL Server. Another instance is SQL Server client to first SQL Server (first hop) to linked SQL Server (second hop), again using the user's credentials the same way. If the security context changes between hops, either by using a "service account" or by using a SQL Server login, then the double hop situation doesn't occur.

With respect to complex multi-domain environments, the big thing is forest level. In Windows 2000 AD you don't have forest level trusts. So domains from one forest can't talk to domains in other forests and do Kerberos authentication. The forest is the Kerberos realm boundary. Windows will drop back to NTLM, which absolutely does not support more than one hop (by design). In Windows 2003 and above domains, Forest-level trusts are permitted, which means forests can have Kerberos authentication between domains in the separate forests.


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #474606
Posted Wednesday, March 26, 2008 4:50 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
He's avoiding the double-hop issue by switching the security context. Previously you were doing this:

client -> web server (as Windows user) -> SQL Server (as Windows user)

now you're doing this:

client -> web server (as Windows user) -> SQL Server (as SQL Server login)



K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #474607
Posted Wednesday, March 26, 2008 5:01 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
Marios Philippopoulos (3/24/2008)
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...


This is what you're effectively doing and why you see the double hop issue:

client (through the web report) -> initial SQL Server (as Windows user) -> linked SQL Server (as Windows user)

That's the double-hop issue. Here's what would have to be configured to make Kerberos work in this situation (and it'll require a domain admin/forest admin to do):

- Service Principal Names (SPNs) properly configured for the initial SQL Server instance (and if this is on a cluster, the Network name will have to be configured for Kerberos authentication)
- Service Principal Names (SPNs) properly configured for the linked SQL Server instance (see above note about cluster config)
- Constrained delegation (I'm assuming Windows 2003/2008 AD) for the service account for the first SQL Server instance permitting it to connect and impersonate to the second SQL Server instance
- If in a multiple domain environment, where you're crossing domains in any way, using fully qualified domain names (FQDNs) in all computer name references (instead of just mysqlserver, mysqlserver.thedomain.net or what have you).

There's a great document which details how to configure and troubleshoot Kerberos delegation related problems here (it's essential reading for CRM 3.0 configurations where all the tiers are on separate servers):

Troubleshooting Kerberos Delegation



K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #474610
Posted Wednesday, March 26, 2008 7:56 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:53 AM
Points: 1,861, Visits: 3,599
There's a great document which details how to configure and troubleshoot Kerberos delegation related problems here (it's essential reading for CRM 3.0 configurations where all the tiers are on separate servers):

Troubleshooting Kerberos Delegation


Great link and feedback, thank you!


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
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 #474723
Posted Wednesday, March 26, 2008 9:04 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Marios Philippopoulos (3/24/2008)
[quote]So at the moment I'm stuck with a less than ideal scenario, and I want to try the Kerberos-auth option...

Good luck. Make sure that you've got some good Networking & AD folks available.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #475193
Posted Wednesday, March 26, 2008 9:20 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
On second thought, maybe you should just brung Brian along. ;)

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #475198
Posted Thursday, March 27, 2008 10:12 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:53 AM
Points: 1,861, Visits: 3,599
rbarryyoung (3/26/2008)
Marios Philippopoulos (3/24/2008)
[quote]So at the moment I'm stuck with a less than ideal scenario, and I want to try the Kerberos-auth option...

Good luck. Make sure that you've got some good Networking & AD folks available.


You hit the nail in the head!

I'm working with MS on the issue, and they have asked me to do a ton of tests that our IT Support team are reluctant to help me with, even though they themselves cannot fix my issue! I'm in danger of having the case closed my MS because of this! Very frustrating, but at least a learning experience in terms of having to work *harmoniously * with other people and get the most done in the process! Will see how this pans out...

Sometimes I find that working with our IT Support team requires the diplomatic skills of a UN Secretary General! :D


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
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 #475549
Posted Thursday, March 27, 2008 10:13 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:53 AM
Points: 1,861, Visits: 3,599
rbarryyoung (3/26/2008)
On second thought, maybe you should just brung Brian along. ;)


I don't think I can afford him... ;)


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
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 #475551
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse