November 30, 2012 at 8:03 am
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 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
November 30, 2012 at 8:42 am
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.
November 30, 2012 at 9:25 am
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
November 30, 2012 at 11:11 am
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 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply