none
Error accessing SQL Server through Visual Studio

    Question

  • Hi

    I'm developing a ASP.NET Web Site, and I'm using the login control.
    Once I drag-and-droppped the login control or accessed the ASP.NET Configuration website, a ASPNETDB.mdf was created and placed under the App_Data folder and then I started to be able to register and use the login control as expected.

    Later on, I needed to switch the database to the SQL Server, through the SQL Management Studio, and then changed the connection string in my Web.config file to use the specific DB, on the server instead of the connection string that was previously defined to use the ASPNETDB.mdf placed under the App_Data, like this:

    <add name="ASPNETDBConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> 

    The problem is, once I changed the connection string I was unable to access the SQL Server, because of an exception that says:

    Login failed for user 'MyPCName\MyUserName'. 

    After this I tried  to create a new login and user in the SQL Server Management Studio and tried to change the connection string to use that username to connect to the database, and it was successful, but, when I try to use the login control or a page where I display user's details (using the Membership or Profile classes) the same exception occurs.

    My connection string is now like this:

    <add name="ASPNETDBConnectionString" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=ASPNETDB;User Id=MyDBLogin;Password=MyPassword"
    providerName="System.Data.SqlClient" />

    I've checked that the mentioned User Id exists in the "Security" > "Logins" tab in SQL Server Management Studio, and there's a user in the database mapped to that login.

    While searching the web trying to solve this problem I found people saying that to solve this error, I could try to activate the "Named Pipes" and "TCP/IP" options, in the "SQL Server Configuration Manager" > "SQL Server Network Configuration" > "Protocols for SQL EXPRESS" and the "SQL Server Browser" under the tab "SQL Server Services", which was already activated or I activated back then.

    After all this, I'm still getting the same error.


    • Edited by P. Silva Thursday, June 20, 2013 10:26 PM
    Thursday, June 20, 2013 10:25 PM

All replies

  • So is that login, MyDBLogin, a user in the ASPNETDB?

    If you look in the SQL Server error log, you should find an entry that matches the failed login attempt. This message includes a state. That state number is the key to why login failed, as detailed in these two blog posts:

    http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx and http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, June 21, 2013 12:04 PM
  • Hi Erland Sommarskog and thank you for your answer.

    The login I'm using, named 'MyDBLogin' in my previous post, has a user under the ASPNETDB mapped to it, and it let's me run the website and see the initial page (Default), displaying the database records I have stored.

    The problem is, if I try to use the login control in ASP.NET when debugging the application, Visual Studio tries to connect to SQL Server using the login 'MyPCName\MyUserName', and that's when the error occurs, although I can access the SQL Server through SQL Server Management Studio using that specific login.

    I've already tried to check if the error logs mentioned anything linked to the failed login, but I couldn't find any trace of it.


    • Edited by P. Silva Friday, June 21, 2013 10:23 PM typo
    Friday, June 21, 2013 10:05 PM
  • How have you concluded that Visual Studio tries to use MyPCName\MyUserName? Not that I know much about Visual Studio and ASP . Net, but it's about web development, isn't it? I'm not sure why Visual Studio would connect to SQL Server directly when you are debugging. Aren't you connecting to the web server and then then web server tries to connect to SQL Server. And if the web server logs in with integrated authentication, that will be through its service account. (Which usually is some machine account, I believe.)

    Just to get things straight: this forum is about SQL Server security. Here we can help you to find out why a certain account cannot login. However, our competence in Visual Studio is more spotty, and if you want to know why there is a different form of authentication when you debug, you will need to ask elsewhere.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, June 21, 2013 10:31 PM
  • I've concluded that Visual Studio is using that account because of the exception it throws, and the message associated to it, that appears in the browser:

    Login failed for user 'MyPCName\MyUserName'. 
      Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
    
     Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'MyPCName\MyUserName'.
    

    I posted here because I think it may have something to do with SQL Server authentication, and I've been searching the web for some time and I haven't found a way to solve the problem.
    What I find strange is that when I created the new login and mapped it to an user in the database, I could connect and get access to the data, but when I try to use specific controls from ASP.NET that will try to connect to the DB, it tries to use my hostname and username to login, and is unable, as mentioned in the previous posts.

    In your opinion, what would be a proper forum to post this problem in?

    Thank you for your help and time.

    Friday, June 21, 2013 11:21 PM
  • If you want to connect with 'MyPCName\MyUserName' and this does not work, this is the place to ask.

    But if you want to connect with MyDBLogin, but your client insists on using something else, we cannot really do more than tell you to review your connection strings. I seem to really that in VS you can configure projects differently for "Release" or "Debug", and maybe you have changed the connection string in one place but not the other.

    I guess the proper forum for you would be something that involves Visual Studio and ASP .NET. My level of knowledge is this area is far below the level where I can point to specific forums.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, June 22, 2013 9:04 AM
  • Well, I first wanted to use Integrated Security, that was what I was using before trying to switch to SQL Server.

    I just find it strange that I can connect with that login through SQL Server Management Studio, but when running the web application using the Integrated Security setting, it throws an error message mentioning the login failed for the exact same login name.

    Monday, June 24, 2013 10:22 PM
  • Regarding your mention to the Web.Release.config and Web.Debug.Config, I tried to add the connection string to those two files, but the result was the same.

    While browsing the web, I found some sites (including here in the forums) that mentioned that when having problems connecting to SQL Server we should check if the SQL Server Browser was running in the SQL Server Configuration Manager, and I confirmed that it's running, but when checking this, I found that SQL Server Agent is stopped and when I right-click it, all options are disabled, I can't start/stop/restart/etc. the service.

    Is there a way this is causing the problem? And how can I solve it?

    Tuesday, June 25, 2013 11:08 PM
  • There are two kind of login problems: 1) when you cannot reach the server 2) when you are not let in when knock on the door. For the former, you need to check the Browser service, firewall etc. Your problem is the latter, so there is no need to check services. Express does not support Agent as I recall, that may explain why you can't start the Agent service.

    If there is no message in the SQL Server error log, right-click the server in Object Explorer (that is the server node itself), select Properties. Check the Security tab. Under Login auditing, make sure that you have not selected "None" or "Successful logins only". I recommend that you use "Failed logins only". Restart the server. Once you have the login errors in the error log, check the blogs I referred you to initially.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, June 26, 2013 7:15 AM
  • I've checked the settings in the "Security" > "Login auditing" and it was already as you say it should be, however, in the logs, I can only see events with messages like: "Using 'xplog70.dll' version '...' to execute (...)", "Attempting to load library 'xplog70.dll' into memory. (...)", "Starting up database 'ASPNETDB'", etc., but I see nothing related to failed logins like the links you posted earlier.

    I just tried to login with a non-existing login, and when I checked the logs, there was a message of the type Logon as expected. However as mentioned before, I don't see any related to the errors the ASP.NET application is throwing.

    • Edited by P. Silva Wednesday, June 26, 2013 10:11 PM additional info
    Wednesday, June 26, 2013 10:07 PM
  • You had "user instance" in your connection string. This is an Express-only feature, which is deprecated. I don't remember where the log for the USER instance is, but the directories are in your user directory.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, June 26, 2013 10:12 PM
  • I had the "User Instance" parameter specified in the first connection string I was using, when accessing the stand-alone ASPNETDB.mdf file, but once I tried to change it to connect to SQL Server specifying the User Id and Password, I didn't use it.

    I just tried to add the User Instance parameter, but once I add it, it won't even show me the initial page (Default), it throws an exception even without using any ASP.NET Membership classes.

    And I continue to be unable to see any log regarding the failed login from the application.

    When you mention "in your user directory" do you mean the "Users\<Username>" directory for Windows 7 or 8?

    Wednesday, June 26, 2013 10:30 PM
  • When you mention "in your user directory" do you mean the "Users\<Username>" directory for Windows 7 or 8?
    Yes. But if you are not using User Instance =true any more, it should not matter.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, June 27, 2013 9:48 PM