Showing posts with label authentication. Show all posts
Showing posts with label authentication. Show all posts

Friday, March 30, 2012

Need Help Authentication loses HttpContext?

We have both a custom data extension and a custom authentication extension for Reporting Services 2000.

Reports with one dataset work great, but reports with more than one dataset fail because the Authentication extension call GetUserInfo no longer has an HttpContext when the second dataset calls into it.

I've seen several people post this same problem on other forums but no answers. Documentation of an msdn article states "the security extension will always be loaded in the Report Server app domain" so I don't understand how we could lose the context.

Additionally, ALL samples of an authentication extension I've looked at have GetUserInfo coded the same way:

Code Snippet

Public Sub GetUserInfo(ByRef userIdentity As System.Security.Principal.IIdentity, ByRef userId As System.IntPtr) Implements Microsoft.ReportingServices.Interfaces.IAuthenticationExtension.GetUserInfo

If Not System.Web.HttpContext.Current Is Nothing _

AndAlso Not System.Web.HttpContext.Current.User Is Nothing Then

userIdentity = System.Web.HttpContext.Current.User.Identity

Else

userIdentity = Nothing

End If

userId = IntPtr.Zero

End Sub

Any help or insights would be GREATLY appreciated - thanks, Ken.

OK, Wanted to share what I have found after opening a ticket with MS.

What we wanted to accomplish:
To dynamically change our connection string per userID so we could hit multiple instances of the same database structure. We did this by using the UserID stored in the HTTPContext. However, multiple datasets in a report caused Reportings Services to spawn new threads to access the data and RS did not persist the HTTPContext to these threads.

The GetUserInfo problem was a red herring- this did indeed always have access to the HTTPContext which was part of our Authentication extension. The Data extension was the real problem as we didn't have the HTTPContext during creation of our Connection object. What we found out from MS is that Reporting Services doesn't guarantee calls to be made on the asp.net worker thread and indeed in some instances even reports with one dataset failed.

Workaround:

Reports build with RS have access to a global variable UserID - this is actually retrieved from either WindowsIndentity or FormsIdentity based on authentication. Thus I added a report parameter UserID with a non-queried default value of =User!UserID to each report, and then created a parameter @.UserID for every dataset with the value of =Parameters!UserID.Value.

In the connection object if I don't have my HTTPContext, I default to a known database so the code doesn't fail. Then in the Command object's execute reader, I loop through the parameters until I find @.UserID. I now can recreate my connection object based on the UserID and set this connection as my Command's connection and return the datareader from the correct database. I make sure the connection is closed by always specifying the CloseConnection behavior during the datareader creation.

Whew!!! A bit cheesy, but it is allowing us to do what we need. Hopefully this will help anyone else that needs it.

Need Help Authentication loses HttpContext?

We have both a custom data extension and a custom authentication extension for Reporting Services 2000.

Reports with one dataset work great, but reports with more than one dataset fail because the Authentication extension call GetUserInfo no longer has an HttpContext when the second dataset calls into it.

I've seen several people post this same problem on other forums but no answers. Documentation of an msdn article states "the security extension will always be loaded in the Report Server app domain" so I don't understand how we could lose the context.

Additionally, ALL samples of an authentication extension I've looked at have GetUserInfo coded the same way:

Code Snippet

Public Sub GetUserInfo(ByRef userIdentity As System.Security.Principal.IIdentity, ByRef userId As System.IntPtr) Implements Microsoft.ReportingServices.Interfaces.IAuthenticationExtension.GetUserInfo

If Not System.Web.HttpContext.Current Is Nothing _

AndAlso Not System.Web.HttpContext.Current.User Is Nothing Then

userIdentity = System.Web.HttpContext.Current.User.Identity

Else

userIdentity = Nothing

End If

userId = IntPtr.Zero

End Sub

Any help or insights would be GREATLY appreciated - thanks, Ken.

OK, Wanted to share what I have found after opening a ticket with MS.

What we wanted to accomplish:
To dynamically change our connection string per userID so we could hit multiple instances of the same database structure. We did this by using the UserID stored in the HTTPContext. However, multiple datasets in a report caused Reportings Services to spawn new threads to access the data and RS did not persist the HTTPContext to these threads.

The GetUserInfo problem was a red herring- this did indeed always have access to the HTTPContext which was part of our Authentication extension. The Data extension was the real problem as we didn't have the HTTPContext during creation of our Connection object. What we found out from MS is that Reporting Services doesn't guarantee calls to be made on the asp.net worker thread and indeed in some instances even reports with one dataset failed.

Workaround:

Reports build with RS have access to a global variable UserID - this is actually retrieved from either WindowsIndentity or FormsIdentity based on authentication. Thus I added a report parameter UserID with a non-queried default value of =User!UserID to each report, and then created a parameter @.UserID for every dataset with the value of =Parameters!UserID.Value.

In the connection object if I don't have my HTTPContext, I default to a known database so the code doesn't fail. Then in the Command object's execute reader, I loop through the parameters until I find @.UserID. I now can recreate my connection object based on the UserID and set this connection as my Command's connection and return the datareader from the correct database. I make sure the connection is closed by always specifying the CloseConnection behavior during the datareader creation.

Whew!!! A bit cheesy, but it is allowing us to do what we need. Hopefully this will help anyone else that needs it.

Friday, March 9, 2012

Need a sysadmin login

We’re running mixed mode authentication on our SQL Servers.To make the server “safer” builtin\admininstrators no longer have sysadmin role on the sql server.If there is only one login with sysadmin role, and we lose track of the password, is there any way to recover it?How could we reset the password or create a new sa account with a new password?This situation has not occurred, but I’m worried about how to recover from it should it occur.This question relates to SQL 2000 and SQL 2005.

David Zokaites,

DBA & Software Engineer

If you deleted the users and have no available sysadmin user you will be locked out of your server for the sysadmin work. Therefore I always suggest to create a user which is not used and only available for these scenarios. Depending on the kind of security level the password can be stored somewhere in a safe or at a secure place.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens,

Thanks for the information. I thought the scenario might lock me out of sysadmin on the sql server. I will certainly make sure we have a secondary sysadmin account to prevent such a disaster.

David

|||

Jens is right as far as SQL Server 2000 is concerned. For SQL Server 2000, you can get locked out; if that would happen, you can recover other databases than master by reattaching them on a different SQL Server instance.

However, in SQL Server 2005, by restarting the server in single-user mode, a local administrator can get access to SQL Server as a sysadmin, so you cannot get yourself locked out of the SQL Server instance.

Thanks
Laurentiu