SQL Server Authentication from IIS

[ryan.d.rembaum]

Hello,

I have read a bunch of topics on this issue but none seems to have
resolved my problem. I have set up a user account in my SQL server
database. e.g. User: TheUser Password: Password and assigned it as a
datareader with the ability to issue SELECT statements on one table.

On a separate IIS server I am running a page that makes a connection to
this database. I have tried a bunch of connection strings and though I
can connect using the SQLDataConnection wizard in .net 2003 Interdev,
Windows ODBC, etc. When I actually browse to test the page using the
same connection string I get the message that:

SQL Server does not exist or access denied.

I do not want to Impersonate and existing account. I have enabled
Anonymous access in IIS. I do not want Windows Integrated Security.
An example of a connection string I that works within .net studio
(development) but now actuall when the ASP page is accessed via
localhost is:

"workstation id=myworkstation;packet size=4096;user id=TheUser;data
source=myserver;persist security info=True;initial
catalog=MyDB;password=password"

What am I doing wrong?

Thanks,
Ryan

 

[Mark Rae]

(development) but now actuall when the ASP page is accessed via

What am I doing wrong?

For a start, you're posting in the wrong newsgroup...

For ASP issues specifically related to database connectivity, you need:
microsoft.public.inetserver.asp.db

 

[Bruce Barker]

you need to turn on mixed security on the sqlserver box, you you can
actually use a standard login.

-- bruce (sqlwork.com)

 

[ryan.d.rembaum]

Mixed security is turned on. I am going to repost this in the news
group Mark suggested. If you can think of some other causes for this
can you please respond to me there! Thanks Bruce!

 

[ryan.d.rembaum]

OK, just got notified by a Microsoft MVP that this is indeed the
correct newgroup to be posting to since this is an ASP .NET issue.
Please, I don't really care too much where people post a response, if
you know the answer this problem has been driving me crazy for days!

 

[ryan.d.rembaum]

OK, just got notified by a Microsoft MVP that this is indeed the
correct newgroup to be posting to since this is an ASP .NET issue.
Please, I don't really care too much where people post a response, if
you know the answer this problem has been driving me crazy for days!

 

[Juan T. Llibre]

If you have setup a specific user in the SQL Server DB, and you have
setup SQL Server to use "mixed authentication", i.e., both Windows and
SQL Server authentication, use this for your connection string :

"SERVER=yourSqlServer;UID=TheUser;DataBase=YourDB;PWD=Password"

 

[ryan.d.rembaum]

PS. This is my fault, I mistakenly wrote ASP page above...should have
read aspX page

 

[ryan.d.rembaum]

Still did not work:


Source File: C:\Inetpub\wwwroot\MFAP\login.aspx.vb Line: 80

Stack Trace:


[SqlException: SQL Server does not exist or access denied.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction) +472

System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString
options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
MFAP.login.DBAuthenticate(String strUID, String strpswd) in
C:\Inetpub\wwwroot\MFAP\login.aspx.vb:80
MFAP.login.BtnLogin_Click(Object sender, EventArgs e) in
C:\Inetpub\wwwroot\MFAP\login.aspx.vb:63
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108

System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
+33
System.Web.UI.Page.ProcessRequestMain() +1277

 

[ryan.d.rembaum]

Let me ask this, does Windows still try to authenticate under the
service account to the SQL Server box even if you are trying to use SQL
Authentication to make the connection. In other words, if were to
impersonate say, myself, with administrative rights (which I don't want
to do), and still try to use SQL Authentication, would the system let
me on? I guess what I am asking is whether I have to setup some WinNT
server permission in order to even attempt to use SQL Authentication?

 

[ryan.d.rembaum]

Let me ask this, does Windows still try to authenticate under the
service account to the SQL Server box even if you are trying to use SQL
Authentication to make the connection. In other words, if were to
impersonate say, myself, with administrative rights (which I don't want
to do), and still try to use SQL Authentication, would the system let
me on? I guess what I am asking is whether I have to setup some
Win2000 server permission in order to even attempt to use SQL
Authentication?

 

[Juan T. Llibre]

If you are impersonating, the credentials sent to SQL Server
will be those of the account you're impersonating, unless you
specify a particular account in the connection string.

For that to be sucessful (specifying a particular account in the connstring),
you need to allow SQL Server authentication, not Windows authentication.

If you are using Windows authentication, the account credentials sent to
SQL Server will be those of the Windows account which ASP.NET is running as.

 

[Ryan]

Also, this is in my web config by default. Do I need to change this???


mode="InProc"
stateConnectionString="tcpip=127.0.0.1:42424"
sqlConnectionString="data
source=127.0.0.1;Trusted_Connection=yes"
cookieless="false"
timeout="20"

 

[Juan T. Llibre]

Not unless you want to maintain state with State Server or SQL Server.

That doesn't affect your SQL Server connections.