Using Widows Authentication and accesssing SQL with SQL Authentication

4 Mar 2006 12:57 AM

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="*" />
      
      
    </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

4 Mar 2006 4:24 AM

David Browne

Show quote

"Valar" <vmanicka***@gmail.com> wrote in message
news:1141433865.402206.290660@j33g2000cwa.googlegroups.com...
> 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="*" />
>      
>      
>    </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.
>

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

6 Mar 2006 6:47 PM

Valar

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 Browne wrote:
Show quote

> "Valar" <vmanicka***@gmail.com> wrote in message
> news:1141433865.402206.290660@j33g2000cwa.googlegroups.com...
> > 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="*" />
> >      
> >      
> >    </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.
> >
>
> 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

4 Mar 2006 5:38 PM

William (Bill) Vaughn

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.
__________________________________

Show quote

"Valar" <vmanicka***@gmail.com> wrote in message
news:1141433865.402206.290660@j33g2000cwa.googlegroups.com...
> 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="*" />
>      
>      
>    </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
>

6 Mar 2006 11:41 PM

Valar

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.