Tuesday, March 27, 2012

A different question about SQL Server Access Denied

I have 2 development servers, both of which I need to use to run an ASP.NET app. The app connects to an external SQL Server with SQL Server authentication only. One of my servers connects properly, but the same exact app running on the second server generates the dreaded SQL Server Access Denied message. A traditional .asp file running on the second server does connect successfully to the database, so I have deduced that the problem is related either to the ASPNET account on the second server, or else to the structure of the server itself or IIS.

I have verified on both machines that IIS uses the ASPNET account for anonymous access. The only difference I can find is that the working server is using NTFS (and the VS_Developers account has full permission on my application's directory), while the problem server is using FAT. Does anyone know if it the FAT file system could be my problem? If so, should I convert to NTFS, or is there another solution? What else could I look at on the problem server? Any help would be greatly appreciated. Thanks.If you are using SQL Server authentication then using IIS anonymous accounts and ASP.Net accounts are not involved in the authentication. What is the exact message that appears? Login failed for user [...] ?

If you are using SQL server with SQL server authentication then in the connection string you should be providing the UID and Password of a SQL Server Login and there should be no "integrated security" clause in the connection string.|||Thanks for the reply. The error message is: "SQL Server does not exist or access is denied". There is no login failure error occurring.

Here is my connection string:
"Data Source=mySQLIPhere,1433;Network Library=DBMSSOCN;Initial Catalog=myDBName;User ID=mySQLUser;Password=mySQLPw;"

As I mentioned, this connection string works fine from one of the 2 development servers as well as from the production web server, so I don't think the connection string is the problem.|||Seems ok to me; if you are willing to do some experiments try these in order.

Verify that you can access mySQLIPhere machine from the machine causing the problem. e.g. by trying to open \\mySQLIPhere or by pinging.

Use query analyzer to connect to SQL Server from the machine causing problem. In "connect to SQL Server" dialog box provide mySQLIPhere as Sql Server name, select Sql Sever Auth, loginname and password. If this works you may experiment rewriting your connection string bit by bit e.g.:
"Data Source=mySQLIPhere; User ID=mySQLUser; Password=mySQLPw;"
"Data Source=mySQLIPhere,1433; User ID=mySQLUser; Password=mySQLPw;"
"Data Source=mySQLIPhere;Initial Catalog=myDBName; User ID=mySQLUser; Password=mySQLPw;"

It is most probably the SQL Server not exist (not found) part that looks true, rather than the access denied part.

No comments:

Post a Comment