Using Widows Authentication and accesssing SQL with SQL Authentication

  • Thread starter Thread starter Valar
  • Start date Start date
V

Valar

Hi

I am working on an application with .NET 2005 and SQL server 2000.

I am using windows authentication for my web application authentication
and trying to connect to the SQL server with SQL authentication
credentials.

My web.config reads as follows:

web application Authentication:

<authentication mode="Windows" />
<authorization>
<allow users="*" />
<!-- Allow all users -->
<!-- <allow users="[comma separated list of users]"
roles="[comma separated list of roles]"/>
<deny users="[comma separated list of users]"
roles="[comma separated list of roles]"/>
-->
</authorization>
<identity impersonate="true" />

SQL Authentication:

<connectionStrings>
<add name="DevDBConnection"
connectionString="Server=MyServer;database=MyDB;uid=MyUser;pwd=MyPwd;"
providerName="System.Data.SqlClient" />
</connectionStrings>

MY IIS directory security is set to "Integrated Windows
authentication". I am validating the user againt the Active Directory
for authentication and reading the user details and groups which works
fine.

But trying to open a connection object fails. I am getting a
"{"Cannot open database requested in login 'MyDB'. Login
fails.\r\nLogin failed for user 'MyUser'."}"

I have tried searching for a solution to my best, but could not solve
this problem.

I hope some one might have come accross the same problem ahd looking
forward for your help.

Thanks,
Valar
 
Valar said:
Hi

I am working on an application with .NET 2005 and SQL server 2000.

I am using windows authentication for my web application authentication
and trying to connect to the SQL server with SQL authentication
credentials.

My web.config reads as follows:

web application Authentication:

<authentication mode="Windows" />
<authorization>
<allow users="*" />
<!-- Allow all users -->
<!-- <allow users="[comma separated list of users]"
roles="[comma separated list of roles]"/>
<deny users="[comma separated list of users]"
roles="[comma separated list of roles]"/>
-->
</authorization>
<identity impersonate="true" />

SQL Authentication:

<connectionStrings>
<add name="DevDBConnection"
connectionString="Server=MyServer;database=MyDB;uid=MyUser;pwd=MyPwd;"
providerName="System.Data.SqlClient" />
</connectionStrings>

MY IIS directory security is set to "Integrated Windows
authentication". I am validating the user againt the Active Directory
for authentication and reading the user details and groups which works
fine.

But trying to open a connection object fails. I am getting a
"{"Cannot open database requested in login 'MyDB'. Login
fails.\r\nLogin failed for user 'MyUser'."}"

I have tried searching for a solution to my best, but could not solve
this problem.
Click to expand...

Nothing magic here. If your connection string specifies SQL authentication,
it will use the user name and password you provide. Can you connect to that
SQL Server using that user name and password with Enterprise Manager or
Visual Studio?

David
 
See my blog post on connecting. I expect that SQL Server is not setup for
SQL Server authentication.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Hi David,

Thanks for your reply.
I am providing SQL authentication in my connection string.

followed the following link to specify the connection string.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/paght000010.asp

When I further dug thorugh the problem, I tried to work with a test
web site and as I debuggged the first inner exception is

'conn.ServerVersion' threw an exception of type
'System.InvalidOperationException' and the conn state is closed., Then
on the conn.open() statement the next login fails exception is thrown.

I could connect to the database with the same userID and pwd specified
in the connection string through VS studic 2005. I would appriciate any
help to solve this problem

Thanks,
Valar

David said:
Valar said:
Hi

I am working on an application with .NET 2005 and SQL server 2000.

I am using windows authentication for my web application authentication
and trying to connect to the SQL server with SQL authentication
credentials.

My web.config reads as follows:

web application Authentication:

<authentication mode="Windows" />
<authorization>
<allow users="*" />
<!-- Allow all users -->
<!-- <allow users="[comma separated list of users]"
roles="[comma separated list of roles]"/>
<deny users="[comma separated list of users]"
roles="[comma separated list of roles]"/>
-->
</authorization>
<identity impersonate="true" />

SQL Authentication:

<connectionStrings>
<add name="DevDBConnection"
connectionString="Server=MyServer;database=MyDB;uid=MyUser;pwd=MyPwd;"
providerName="System.Data.SqlClient" />
</connectionStrings>

MY IIS directory security is set to "Integrated Windows
authentication". I am validating the user againt the Active Directory
for authentication and reading the user details and groups which works
fine.

But trying to open a connection object fails. I am getting a
"{"Cannot open database requested in login 'MyDB'. Login
fails.\r\nLogin failed for user 'MyUser'."}"

I have tried searching for a solution to my best, but could not solve
this problem.
Click to expand...

Nothing magic here. If your connection string specifies SQL authentication,
it will use the user name and password you provide. Can you connect to that
SQL Server using that user name and password with Enterprise Manager or
Visual Studio?

David
Click to expand...
 
ok.. I worked on this the problem is that I gave the provider detail
also in my connection string.

<connectionStrings>
<add name="DevDBConnection"
connectionString="Server=MyServer;database=MyDB;uid=MyUser;pwd=MyPwd;"
providerName="System.Data.SqlClient" />
</connectionStrings>
the culprit is providerName="System.Data.SqlClient"

I tried the same User Id and pwd in a UDL file, Visual studio 2005 etc
everthing worked. so I started playing around the conn.string. When I
tried without the Provide Name attribute it worked :).

Thanks for your support and reply.
 
Back
Top