October 12, 2010 at 6:41 am
I'd like to bring this topic back up because I don't quite understand the last post.
My background:
Application running on Windows 2003 server
ApplicationPool username is 'dotnetsysadmin'
My application opens a web browser which is an aspx page (I bring that up because I'm not sure if the app would be sending the "System.Current.Username", or whatever it is, or if it's 'dotnetsysadmin'
I've run these different versions of the sp_addlinkedsrvlogin script:
EXEC sp_addlinkedsrvlogin @rmtsrvname = N'labdevrpt', @useself = N'True', @locallogin = 'IDOT\dotnetsysadmin', @rmtuser = 'dotnetsysadmin'
EXEC sp_addlinkedsrvlogin @rmtsrvname = N'labdevrpt', @useself = N'False', @locallogin = 'IDOT\dotnetsysadmin', @rmtuser = 'dotnetsysadmin'
For the first of those, I get this error in my application:
An error occurred trying to retrieve the RDARS data. Check the ExceptionLog for more details. A SqlClient.SqlException happened in ExecuteFill! Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
And for the second, I get this error:
An error occurred trying to retrieve the RDARS data. Check the ExceptionLog for more details. A SqlClient.SqlException happened in ExecuteFill! Login failed for user 'dotnetsysadmin'.
The previous poster said to RightClick->Properties->Security->Add, then something after that, but where do I right-click? Or does anyone know how to fix this?
BTW - I've sent in a request to our DBA to run the 2nd version of that script while defining the username AND password, because our server team won't let us know what the application password is...
October 12, 2010 at 7:07 am
Is this a web application?
Does the SQL Server you are trying to reach on the server or is it on another server?
If it is on another server I bet you are getting into a kerberos problem.
If you want to check your Keberos for a web server I found this utility great for troubleshooting my problems.
http://www.iis.net/community/default.aspx?tabid=34&g=6&i=1887
October 12, 2010 at 7:16 am
Also, This is the way I run all of my SQL Instances and I don't have any problems with login issues. It may or may not be correct but it works well for me.
1. Enable TCP/IP Client protocol only. (Disable the rest)
--Change Port number
2. Alias the Instance name with the new port number. (This is the key)
3. Setup Alias on the agent so I can join my MSX/TSX Correctly.
4. When I create a linked server I use with trusted connections.
--Always make sure I alias the remote machine.
5. I always use AD Accounts/Groups to contorl my security. I never assign a local and/or group/AD Account security to remote login.
October 12, 2010 at 7:37 am
Matthew Clifford (10/12/2010)
Does the SQL Server you are trying to reach on the server or is it on another server?
If it is on another server I bet you are getting into a kerberos problem.
If you want to check your Keberos for a web server I found this utility great for troubleshooting my problems.
http://www.iis.net/community/default.aspx?tabid=34&g=6&i=1887%5B/quote%5D
"Is it a web application" - Kind of. The part of the application that is trying to do the linkedserver query IS a web page.
The application runs from multiple application servers, then there is a SQL Server on a different server, then there is a 2nd SQL Server which is where my data resides (hence the linkedserver query). So I'm guessing it's the kerberos thing that you said, since the application and database are on different servers...I'll try your link.
Thanks
October 12, 2010 at 7:39 am
Matthew Clifford (10/12/2010)
http://www.iis.net/community/default.aspx?tabid=34&g=6&i=1887%5B/quote%5D
Unfortunately, I am running IIS 5.0 and this only works with 6 and 7. Damn!
October 12, 2010 at 7:40 am
I would be willing to put lunch on it that it is a kerberos issue.
That link that I sent you helped me with SSRS and using pass thru authentication with multiple hops.
One key point...do what it says...Slow down and read the instructions vert carefully!
October 12, 2010 at 8:32 am
Matthew Clifford (10/12/2010)
I would be willing to put lunch on it that it is a kerberos issue.That link that I sent you helped me with SSRS and using pass thru authentication with multiple hops.
One key point...do what it says...Slow down and read the instructions vert carefully!
Ok, I found a version for IIS 5.0...Installed it and this jumped out at me:
Process Identity
Domain Account? IDOT\dotnetsysadmin is a valid domain account. Explanation
Only accounts that are in Active Directory (domain user or computer accounts) can participate in Kerberos. This is because it is the domain controller (a.k.a. Key Distribution Center) that grants Kerberos Tickets. Local accounts are not recognized by Active Directory and cannot obtain Kerberos credentials. SYSTEM, Network Service, and Local Service are considered domain accounts as long as the server is part of a domain, however, Local Service cannot obtain network credentials for communicating with back-end servers.
Has a valid SPN? This tool is unable to verify that the proper SPNs are set because the WrkstaInfo.dll C# ActiveX control failed to load in Internet Explorer. This component is used to check client-side name resolution. Name resolution is needed to determine what SPN was requested by the client machine. Explanation
To help this tool give you accurate SPN information:
Open a CMD prompt on the client machine
Type "ping localhost" (this is based on the current URL being used)
Copy the resulting Fully Qualified Domain Name into the clipboard
Paste the FQDN after "resolveName=" in the address line of IE.
Existing SPN's for IDOT\dotnetsysadmin:
Unable to obtain ServicePrincipalName information.
An SPN (Service Principal Name) is much like a UPN (Universal Principal Name). UPN's are unique names for identifying a domain user account whereas SPN's are unique names that usually identify a domain computer account. An example of a UPN would be "myAlias@america.microsoft.com" instead of the standard "AMERICA\myAlias". Domain computers are granted two ServicePrincipalNames of type "HOST" by default when they are joined to the domain. Domain users are not granted any ServicePrincipalNames because their unique identifying name is generally a UniveralPrincipalName.
Duplicate SPNs? Duplicate ServicePrincipalName information for the IDOT\dotnetsysadmin account could not be determined. Explanation
Without first knowing the requested SPN, this tool cannot determine what accounts might have that duplicate SPN set. Please follow the steps in the 'Has a valid SPN?' section to correct this warning.
Because an SPN is essentially a "username" for a service, a given SPN cannot be set on more than one account (user or computer account) at a time. If the same SPN is set on more than one Active Directory account, Directory Services will frequently create a ticket based on the account that has the "duplicate SPN". This "duplicate" is then sent to the server/service but the service will fail to decrypt the ticket propertly since it was created based on a different account.
Trusted for Delegation? Account IDOT\dotnetsysadmin is not Trusted for Delegation. Explanation
It is NOT necessary to trust this account for delegation unless it needs to PASS credentials onto another server. "Trust for delegation" does not effect whether or not a user can connect to IIS with Kerberos. It only effects whether the user's token can be passed on to another backend computer from the IIS server. DO NOT trust this computer or any other account for delegation when attempting to get Kerberos working unless a process running under that account will also be passing credentials.
By default when using "Integrated Windows Authentication" through IIS, the authenticated user is granted a "Network Token" that cannot be passed to any other computers. When using delegation, the Token then can be passed to other computers as long as the user successfully connected with Kerberos credentials. This can have security implications because trusting an account for delegation essentially means that the account is being granted authority to do some actions that normally only a domain controller would be able to do.
The dictionary definition for "delegation" is when one is appointed to act on behalf of another. So in Windows terms when an account is trusted for delegation that means that the account is trusted to act on behalf of an authenticated user.
Authenticated User
Domain Account? The domain or workstation membership of Anonymous User could not be verified. Explanation
Because anonymous authentication does not identify the end user that is connecting to IIS, this authentication method cannot be used to support delegation.
Authentication Method? You have connected from your browser to IIS using Anonymous authentication. Explanation
Because anonymous authentication does not identify the end user that is connecting to IIS, this authentication method cannot be used to support delegation.
View authorization header (this is for informational purposes only)
Overall Status
Will Delegation Succeed? The current configuration will not succeed in Delegating credentials with Kerberos. Explanation
If any items listed above are flagged as failures they will prevent Kerberos and/or delegation from succeeding. Please address the failures that are listed by reading the relevant "Explanation" sections.
If this ASP.NET application does not provide adequate assistance to help resolve your problem please see Troubleshooting Kerberos Delegation.
That first part says that it's not trusted unless it needs to pass the credentials. Then it says that you should NOT trust when attempting to get kerberos working. My question, should I trust it here?
October 12, 2010 at 8:33 am
Sorry, the section called "Trusted for Delegation" is what jumped out at me...
October 12, 2010 at 9:00 am
If I remember correctly you need to trust your accout that is running your SQL Service for delegation.
Add your SQL Service Account to SeEnableDelegationPrivilege under user rights.
I would only trust specific accounts for delegation not the entire machine.
October 19, 2010 at 12:04 pm
Just to give you an update on this. I met with our DBA and our AD guy this morning and we got this working. Although we did not use the scripts. We had to go in thru Mgmt Studio, create the LinkedServer, and just specify the [native SQL] username and password. Everything works fine. We were never able to get it to work with an AD username.
October 19, 2010 at 12:08 pm
Thanks for the update. Don't take this the wrong way but typically what you just did is the way most DBA's deal with the issue. It's easy and it gets the developers out of there hair. In the future if you guys try to make it work I keep and eye on this thread and you can post issues.
November 9, 2010 at 8:28 am
I had exactly same problem. There are 3 machines. A is my local computer. B, C are sql servers. I was connecting from my local(A) to B server and from B server I was running a procedure to verify some data from C server. I was getting "Msg 18456, Level 14, State 1, Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" error.
--------------------------------------------
Solution:
--------------------------------------------
There was a linked server defined on server B and if you change security section for Linkserver it problem disappears.
Instead of using "Be made using the login's current security context", you should use "Be made using this security context" option and you need to provide "remote login and password". This option is the last option on the linkserver security section.
I hope it helps to everyone...
Rabia
November 9, 2010 at 8:34 am
Rabia Cinar Yuksel (11/9/2010)
I had exactly same problem. There are 3 machines. A is my local computer. B, C are sql servers. I was connecting from my local(A) to B server and from B server I was running a procedure to verify some data from C server. I was getting "Msg 18456, Level 14, State 1, Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" error.--------------------------------------------
Solution:
--------------------------------------------
There was a linked server defined on server B and if you change security section for Linkserver it problem disappears.
Instead of using "Be made using the login's current security context", you should use "Be made using this security context" option and you need to provide "remote login and password". This option is the last option on the linkserver security section.
I hope it helps to everyone...
Rabia
Sorry I didn't post my results, but that's EXACTLY what I had to do.
November 9, 2010 at 8:47 am
Matthew Clifford (10/12/2010)
If I remember correctly you need to trust your accout that is running your SQL Service for delegation.Add your SQL Service Account to SeEnableDelegationPrivilege under user rights.
I would only trust specific accounts for delegation not the entire machine.
If the user is in IIS5 which runs in Win2k trusted for delegation is not valid because Delegation comes with Win2003 some of the reasons creating an account to use is one of the valid ways to resolve this problem.
Kind regards,
Gift Peddie
November 9, 2010 at 9:33 am
If the user is in IIS5 which runs in Win2k trusted for delegation is not valid because Delegation comes with Win2003 some of the reasons creating an account to use is one of the valid ways to resolve this problem.
Kind regards,
Gift Peddie
What if you are running Server 2008?
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply