ASP.NET cannot connect to SQL Server 2005

Question

Hey all,

I'm trying to create an ASP page that has a bridged connection with an SQL Server 2005 database (separate sever from the ASP's server). For this I am trying to use a Windows Authentication setup. I have my name with full rights to the SQL server yet I am still getting the error 'Login failed for user COMPANY\name'. To see if this was just a Windows Authentication problem I tried to do a SQL Server Authentication; This also will not let me login. I have no idea why there would be a problem with the SQL Authentication, but I could really use some help figuring out why the Windows Authentication won't work. Thank you for your time.

Regards,

Franco

Answer 1

Dont use Windows authentication for this kind of connection.

Can you post a copy of the connection string (sanitized - change the password) you are using for both WA and SQLauth?

Answer 2

I assume these are on the same domain (since you mentioned COMPANY\name)?

Manke sure SQL Server is set to allow remote connections. (Surface Area Configuration for Services and Connections)

If you are using Windows Auth, are you trying to specify a single user to connect as, or is the current user's identity used? If you wish to speify a single user to always connect as, I would recommend a SQL Server account instead of a domain account.

Answer 3

Try stripping out the domain part of your username - COMPANY\User - it may be interfering with the authentication.

Also - on the SQL Server (if you have access), have you tried connecting to the server via localhost? It could just be that your user doesn't have enough priveledges.

SQL Server Auth - have you created a user and attached them to a database with read priveledges?

Answer 4

When you installed SQL Server, did you configure it to use mixed mode authentication? Also can you connect to using SQL Management Studio directly, using SQL authentication or Windows authentication?

Answer 5

You said, when you tried logging into SQL Server using your SQL Authentication (I guess it is SQL Server user id and password), it didn't let you in. That means there is some problem at the SQL Server end. Make sure that your SQL Server is setup for mixed mode authentication. Meaning it should be able to let you in using windows authentication as well as if you provide sql server user id and password.

That is one pointer. Another point as everyone here is saying, don't use windows authentication to connect to SQL Server from ASP page. For this to work you need to setup the domain user to be a SQL Server user. Today you are the only one using this page, how about tomorrow when you need to give this page to entire org?

Answer 6

I'm pretty sure that it can't be on the SQL Server end because I am able to connect from a completely different server then the one I am trying to connect from now. Could it be a problem from the server which I am using? If so, what are possible solutions?

Talking to some fellow employees they say they haven't tried to connect to this server in the past. I just find it weird that I am able to connect from one server but not another.

Answer 7

Here's a checklist of steps to go through when you have trouble making a database connection from an ASP.NET application

1. Can you ping the database server from the ASP.NET server?
2. Can you connect to the database using SQL Authentication and Management Studio from the ASP.NET server?
3. Write a page with a very simple, small-return query to use as a test. Can you query the database with the test page? Don't rely on any web.config entries, manually enter all connection string and query data (don't leave this test page on the server longer than necessary to test)
4. If your test page fails, what is the error? If you test from the ASP.NET server console you should get a detailed error that may indicate the problem.
5. If you have multiple server databases, make absolutely sure you're connecting to the server you think you are ;)

General Tips: Management Studio is probably the single best test- if you can connect through Windows and SQL Authentication via Management Studio from the ASP.NET server, then the problem must be with your ASP.NET application configuration.

You mentioned that you are getting an explicit authentication error that "COMPANY\name" is not authorized. Are you running a domain controller? Is "COMPANY" the domain name? If not, ASP.NET may not be authenticating against the database using your domain account.

If you must use Windows authorization remember to enable impersonation in the web.config:

<identity impersonate="true" />

You will also need to disable Anonymous access and enable Integrated security in the IIS configuration for your site.

HTH.

Answer 8

how can i connect sql server with asp.net 2.0 what will be the connection string if i have windows authentication and what can i do to run my asp application in local browser.

[email protected]