SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS Data Source using Windows integrated security gives Login failed for user 'NT...


SSRS Data Source using Windows integrated security gives Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Author
Message
Mike.Hahn
Mike.Hahn
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3713 Visits: 722
Hi All,

On my data source when I select Windows Integrated Security as the connection method I get login failed for user "..Anonymous..". I would assume it would use the service account but apparently not. Since SSRS stopped using IIS from 2008 I have no idea how to fix this issue or why is it even using the anonymous... It's only been about a month since I started working with 2012 so not too sure what I need to modify. Screen shot is attached.
Any advice is greatly appreciated.
Whatever other information you need let me know.

EDIT: most posts I've seen talk about double hops through sharepoint but I'm in the report manager and looking at the data source directly from there.

---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar Hehe
I want a personal webpage Cool
I want to win the lotto :-D
I want a gf like Tiffa w00t Oh wait I'm married!:-D
Attachments
SSRS_DS.PNG (346 views, 24.00 KB)
Scott Ohar
Scott Ohar
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3214 Visits: 654
If your shop allows it just create a new windows user account and put it in Credentials stored securely in the report server and check use as windows credenitals when connecting to data source and make sure it has the correct permissions for what the report is doing.

Bleeding Edge - just means alot of sleepless nights
thisted
thisted
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1859 Visits: 344
That error message is almost always related to the "Double Hop" problem. The easiest way to fix it is to make sure all trusted connections are connecting using kerberos instead of NTLM. The alternative would be to use a SQL user instead of trusted auth.

Setting up kerberos is not a simple undertaking, but will resolve this problem and other problems you'll likely experience in the future. I have a many page document specific to our environment (so I can't share all of it) that describes how to set it up. My opening description is as follows:

Configuring SPN's Allows for machines to connect to SQL Server with Kerberos authentication instead of NTLM. Microsoft does not allow NTLM authentication for connections using what is known as the "Double Hop". Microsoft does allow connections using Kerberos to connect using the "Double Hop".

A typical Scenario where you would connect through the "Double Hop" would be by using a linked server. If you connect to SQLSRV1 using Trusted Windows Authentication, and then Select from a linked server that is configured to pass the login's current security context, you have just made a "Double Hop".

If you attempt to do this utilizing NTLM you will get an error saying "Login Failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" This error will occur whether you're trying to connect through IIS first, through SSIS, SSRS, or even while trying to do bulk file operations to a file server as the second hop.



Run the following code on your backend SQL server to determine whether the connections to the server are using kerberos or not.


SELECT sys.dm_exec_connections.session_id AS SPID,
sys.dm_exec_connections.connect_time AS Connect_Time,
DB_NAME(dbid) AS DatabaseName,
loginame AS LoginName,
sys.dm_exec_connections.auth_scheme as Auth_Scheme,
sys.dm_exec_connections.net_transport AS Net_Transport,
sys.dm_exec_connections.protocol_type as Protocol_Type,
sys.dm_exec_connections.client_net_address as Client_Net_Address,
sys.dm_exec_connections.local_net_address as Local_Net_Address,
sys.dm_exec_connections.local_tcp_port as Local_TCP_Port
FROM sys.sysprocesses
Right Outer JOIN sys.dm_exec_connections
ON sys.sysprocesses.spid=sys.dm_exec_connections.session_id
Order By Auth_Scheme, Net_Transport


Mike.Hahn
Mike.Hahn
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3713 Visits: 722
Thanks I don't have permission to run that script but I'll see if I can get someone to tell me or run it.

---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar Hehe
I want a personal webpage Cool
I want to win the lotto :-D
I want a gf like Tiffa w00t Oh wait I'm married!:-D
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search