SQL Connection Error

[Sparky Arbuckle]

I am trying to connect to a SQL server that is on a different machine
in the building. I can view the contents in Enterprise Manager and
Query Analyzer. The problems arise when I try to connect to this
database from a web application. Apparently I need an actual login and
password on the SQL server for connecting from a web application. Is
this always the case or are there ways around this. If it means
anything, I am using Visual Studio .NET 2003 and have imported all
relevant (I believe so) namespaces for this to work.

Connection string:

<add key="SQLConnection" value="Data Source=ServerName;Initial
Catalog=DatabaseName />


Testing connectivity:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Try

Dim objSQLConn As New
SqlClient.SqlConnection(ConfigurationSettings.AppSettings("SQLConnection"))
objSQLConn.Open()
If objSQLConn.State = ConnectionState.Open Then
Label1.Text = "SQL Connection is open"
Else
Label1.Text = "SQL Connection is closed"
End If

Catch sqlxcp As SqlClient.SqlException
Label2.Text = sqlxcp.ToString
Label3.Text = sqlxcp.Message
Label4.Text = sqlxcp.Source
Label5.Text = sqlxcp.Number
Finally
End Try

End Sub
______________________

I can view the data just fine in VS .NET's dataset filler but when I go
to actually connect via web application I get a plethora of errors. Any
suggestions?

 

[Sparky Arbuckle]

I guess I should probably provide the errors:

Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.

..Net SqlClient Data Provider

18452

I can view the SQL server tables and data just fine using my Windows NT
account but not when I try to connect from a web application. Any
suggestions?

 

[Clint Hill]

You do need to include a username and password unless you want to add
ASPNET account to SQL Server (not recommended by me). Right now you are
telling SQL to use a Trusted Connection (default if not specified).

your connection string might look like this:
data source=(LOCAL);initial catalog=YourDataBase;uid=UserName;pwd=Password;


Clint Hill
H3O Software
http://www.h3osoftware.com

 

[Sparky Arbuckle]

Thanks for the reply Clint.

I am running this app from my inetpub folder until I am able to upload
it to a web server. I want to test everything first. I tried adding
uid and pwd to my connection string and now I am getting the error:

Login failed for user 'UserName'.

..Net SqlClient Data Provider

18456

In VS when I right-click the adapter to preview dataset for this
connection I can view just fine so I know I'm able to connect to this
database. My windows authentication is what I used to connect to the
SQL server via Enterprise manager and Query Analyzer. This works
flawlessly. The only problem is when I try and connect via web app.

Does this make sense or do I need to clarify more?

 

[Clint Hill]

You have two things conflicting that are confusing you.

You can connect to SQL using Windows Auth, (through VS.NET). You cannot
connect (as you have found) through your Web app. The reason is the web
app is running under an account IUSR_YOURSERVERNAME that uses ASPNET
account. This account doesn't have permissions to your SQL Server. You
can add it and assign it permissions in your database, but I wouldn't
recommend this.

I would however recommend creating a new SQL Server user and add it to
the database you are connecting to. Then use that username and password
in your connection string. You can also test this when using Server
Explorer in VS.NET


Clint Hill
H3O Software
http://www.h3osoftware.com

 

[Marina]

When you running VS.NET, you are running it as you. So the database
connections go through as you. Integrated security works.

When you run a web page, ASP.NET does *not* run as you, just because you are
the one who requested the page. It runs as the ASPNET user (or some other
user if you changed this setting). So if ASPNET (or the other user) does
not have access to the database, then you get this error.

'UserName' was an example he gave. Meaning you need to replace it with a
real user name on your sql server, and same thing goes for the password.
Unless you happen to have a user called 'UserName' with a password
'Password' - which I highly doubt anyone would.

I recommend you do some googling around for information on ASP.NET's
security model and how it related to ADO.NET.

 

[Clint Hill]

I just realized that you can add this to your web config:

<identity impersonate="true">

This would force ASPNET to use your Windows Account. then your sql
connection string can stay as you had it. However, then, each person
connecting to your database will need a Windows Account on the SQL
Server and associated to the database.


Clint Hill
H3O Software
http://www.h3osoftware.com

 

[Sparky Arbuckle]

You 2 make so much sense that it's ridiculous. Thanks a million Clint &
Marina!

 

[Sparky Arbuckle]

When I add identity impersonate="true" I get:

Login failed for user '\'.

 

[Denny Boynton]

My guess is that you have anonymous access turned on for the web
application and your application cannot retrieve a value of the
AUTH_USER server variable. Try turning anonymous access off in your web
app and see if that works.

However, you'll definitely want to change this before moving your
application into production as every user of your application would
need to have access to the SQL Server. As is mentioned in the thread
above, setting up a specific SQL Server login account and using that
username and password is the best means of creating dependable and
secure access to the database from your web application.

HTH

Thanks,
Denny Boynton

 

[Paul Clement]

¤ I am trying to connect to a SQL server that is on a different machine
¤ in the building. I can view the contents in Enterprise Manager and
¤ Query Analyzer. The problems arise when I try to connect to this
¤ database from a web application. Apparently I need an actual login and
¤ password on the SQL server for connecting from a web application. Is
¤ this always the case or are there ways around this. If it means
¤ anything, I am using Visual Studio .NET 2003 and have imported all
¤ relevant (I believe so) namespaces for this to work.
¤

Some pieces of information need to be confirmed here. What level of authentication are you using for
your web app? Anonymous? Basic? Integrated Windows?

If SQL Server is not installed on your web server you cannot delegate security when your web
application is configured for Integrated Windows security w/o configuring your environment for
Kerberos.

There are alternative solutions, such as using Basic authentication, but we would need to know what
type of security you need for your web application.

You may also want to view the following document:

http://msdn.microsoft.com/library/d...y/en-us/vsent7/html/vxconaspnetdelegation.asp


Paul
~~~~
Microsoft MVP (Visual Basic)