Authenticating with Sql Server using domain account as app pool Identity

[Former Member]

I am trying to use a domain account for an Isolated IIS 6 app pool Identity. The app pool runs the asp.net 4.0 frame work app fine and authenticates to sql fine from code using integrated security. However when I try to load a crystal report into viewer or stream to pdf crystal reports a database login failure, displayed in top of viewer control. Running a trace against the sql database there is no login attempt being made before the failure occurs.

I do not want to impersonate the logged in user. I just want to control access to the database using the app pool domain account as the login.

[former_member183750 · Accepted solution]

As you mention asp.net 4.0 frame work, the assumption is that you are using CRVS2010(?).

Have a look at the article [Using Integrated and SQL Authentication in .NET Applications|https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/b021e47e-be1d-2b10-c6b2-efa9db3abd6b]. The article was written before CRVS2010, but there is no functional change - all should work in CRVS2010 as described in the article.

Ludek

Follow us on Twitter http://twitter.com/SAPCRNetSup

Got Enhancement ideas? Try the [SAP Idea Place|https://ideas.sap.com/community/products_and_solutions/crystalreports]

[Former Member · Accepted solution]

None of the scenarios listed in the document actually cover my situation. The closest scenario requires that impersonation and delegation be used to pass the user accessing the sites credentials to the sql server. In our case we are authenticating the user and maintaining application security internally and only want to have crystal use integrated security using the app Pool account as the user context for everyone. Since it is logged into the web server its connection to the sql server is a single hop and does not require delegation as evidenced by other ado.net processess working fine.

Additional testing might be pointing to a different problem. I switched the application to doing using a sql login using the following procedure to set the connection info for the report before loading it into the web viewer. This procedure assumes connection to a single database is needed for the report.

The result is the same it does not try to hit the database as verified by a sql Trace and the error reported in the viewer is database Login failed eventhough it didn't try.

Public Shared Function SetCrystalConnectionString(ByVal rptDoc As ReportDocument, ByVal strConn As String) As ReportDocument

Dim sqlCon As System.Data.SqlClient.SqlConnectionStringBuilder = New System.Data.SqlClient.SqlConnectionStringBuilder(strConn)

Dim CConInfo As New ConnectionInfo

Dim tables As Tables = rptDoc.Database.Tables

Dim t As Table

Dim tlogInfo As TableLogOnInfo

Dim rs As Section

Dim ro As ReportObject

Dim srpt As ReportDocument

'create connection Info object from connection string

CConInfo.IntegratedSecurity = False

CConInfo.ServerName = sqlCon.DataSource

CConInfo.DatabaseName = sqlCon.InitialCatalog

CConInfo.UserID = sqlCon.UserID

CConInfo.Password = sqlCon.Password

For Each t In tables

tlogInfo = t.LogOnInfo

tlogInfo.ConnectionInfo = CConInfo

t.ApplyLogOnInfo(tlogInfo)

Next

'check for sub reports and update connection strings.

For Each rs In rptDoc.ReportDefinition.Sections

For Each ro In rs.ReportObjects

If ro.Kind = ReportObjectKind.SubreportObject Then

Dim srptO As SubreportObject = ro

srpt = srptO.OpenSubreport(srptO.SubreportName)

For Each t In srpt.Database.Tables

tlogInfo = t.LogOnInfo

tlogInfo.ConnectionInfo = CConInfo

t.ApplyLogOnInfo(tlogInfo)

Next

End If

Next

Next

Return rptDoc

End Function

[former_member11696 · Accepted solution]

Hi Pat,

That do should work for you, specifically this one:

'Setting the third parameter to True will allow SSO and will not prompt

report.DataSourceConnections(0).SetConnection("dbServer", "Northwind", True)

Also, you are setting Trusted to false and passing in a user name password:

CConInfo.IntegratedSecurity = False

CConInfo.ServerName = sqlCon.DataSource

CConInfo.DatabaseName = sqlCon.InitialCatalog

CConInfo.UserID = sqlCon.UserID

CConInfo.Password = sqlCon.Password

Set the SSO to True and comment out the User/PW lines, not required if trusted it used. Setting if to False tells CR to always prompt and ignore the Users/PW.

Also, assuming the account the App Pool is running under is also in SQL Server then it should work.

Another option is to set the log on info in your web.config file and then simply read it for log on info.

Don

Edited by: Don Williams on Sep 15, 2011 1:42 PM