ASP.NET, Crystal Reports, SQL Server, and Windows Authentication?

[Doug]

Simple question, hoping for a quick answer:

Using Visual Studio .NET 2003 and Crystal Reports 10, is it possible to
connect to a SQL Server 2000 database using Windows
Authentication/Integrated Security?

We have been unable to make it work. And I have read several posts
suggesting that it cannot be done, but I'd like to have a definitive
answer.

Thanks.

 

[OHM \( One Handed Man \)]

Yes.

When you create the report you can choose OLE DB SQL provider and this will
give you the opportunity to use standard or integrated security.

However, I normally us datasets because it give you more control over the
data.

 

[Doug]

Thanks.

Do you have any simple examples showing the dataset method with Windows
Authentication?

Also I was told (by my company) that we are not allowed to use OLE DB
(ADO) and have to use ODBC (RDO).

 

[sirfunusa]

We use it here all the time. And RDO? A technology 4 years old?

You will need to turn on Basic Authentication on your web server. Is
SQL on the same box as IIS?

Jeff

 

[OHM \( One Handed Man \)]

IIS has got nothing to do with this question. I will answer this later when
I get to work and give you the OP an example.

 

[\(OHM\) One Handed Man - aka Terry Burns]

OK. The method is essentially this

1.) Create a strongly type dataset , you can do this by opening up the
server explorer in VS and selecting the table you need from the appropriate
server. Drag this to your webform and this will create an sqlConnectiona dn
sqlDataAdapter ( depending on your server source ). RightClick on the
DataAdapter to pick Generate DataSet and then give it a meaningful name. You
will use this XSD Class Template in the crystal report definition.

2.) Create a Crystal Report and choose the Project Options Datasource and
pick the DataSet template you just created in step2. add fields, grouping
and totalling as required.

3.) Step three required you fill the dataset

dim MyDataSet as new YourNewDataSetTemplate
sqlDataAdapter.Fill( YourDataSet )

4.) Now Create a new object from the Crystal Report you creates

Private crpt As CrystalDecisions.CrystalReports.Engine.ReportClass

5.) The Code.

crpt = new YourCrystalReportName

crpt.SetDataSource(MyDataSet)

Me.CrystalReportViewer1.ReportSource = crpt

Me.CrystalReportViewer1.DataBind()

HTH

 

[Terence Tirella]

AFAIK the answer is no, you cannot use Windows Authentication for SQL
Server if you're going to deploy your reports via an ASP.NET web site.
The problem is that the report components do not run as you and don't
support impersonation. You can have the application run as a user with
database credentials but this is a security risk.

http://support.businessobjects.com/library/kbase/articles/c2010867.asp

 

[Doug]

"At this point in time, Crystal Reports for Visual Studio .NET does not
support impersonation, so this method will not work with Crystal
components. "

So if I'm using Crystal Reports for VS.NET, I can't use Windows
Authenticaion, but if I upgrade to Crystal Reports 10 I can?

 

[OHM \( One Handed Man \)]

He's wrong. You can use it either natively using the crystal reports logon
or using datasets as I have described. Trust me, I use it both ways as
appropriate.

Impersonation has absolutely nothing to do with it as the ASPNET account is
what accesses the database not the user if your using datasets. And if you
are using CR's own access method then you can still use Windows Integrated.

 

[Terence Tirella]

Maybe I misunderstood the orignal poster's question, I took to be
asking if it's possible to create a report that connects to SQL Server
2000 using integrated authentication (i.e. when you create the report).
That is not supported AFAIK.

If you use a dataset and populate it manually with your own ADO.NET
code then that's a different situation.

Again, you can have your ASP.NET worker account be a user in SQL Server
but that's not recommended and I don't know for sure if that even
works, I've never tried it and have used SQL authentication instead.