locked
Users in SQL Express server RRS feed

  • Question

  • This is my connection routine for an SQL Express database I'm using with ASP.NET and C#

            connectionString = "server=.\\SQLExpress;uid=sa;pwd=;database=cartridge;" +
            "Integrated Security=True;";
            connection = new SqlConnection(connectionString);
            connection.Open();

    When I run the database in "debug" mode through the VWD IDE the connection is made and I get query data etc, but when I try to access the database from IIS I get a login failed message, I assume this means I must create another SQL user? I can't find any option to do this in the GUI.

    Please advise on this issue, it will be much apprecaited.

    Alex Ellis

    Server Error in '/print' Application.

    Cannot open database "cartridge" requested by the login. The login failed.
    Login failed for user 'D3MF2Q1J\ASPNET'.

    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: Cannot open database "cartridge" requested by the login. The login failed.
    Login failed for user 'D3MF2Q1J\ASPNET'.Tongue Tied

    Source Error:

    Line 27: "Integrated Security=True;";
    Line 28: connection = new SqlConnection(connectionString);
    Line 29: connection.Open();
    Line 30: }
    Line 31:

    Source File: c:\projects\CartridgeTracker\App_Code\CartAPI.cs    Line: 29

    Friday, July 22, 2005 2:51 PM

Answers

  • Hi,

    The problem is you are using a Windows user for a SQL Authentication. Anyways I will give a step by step procedure this time.

    Enabling SQL Authentication

    STEP1:
    You need to enable SQL Authentication on the machine. This is complex in SQLExpress and you need to edit the registry. Copy the following lines of text onto a Notepad and save it with extention .REG.
    -------------COPY BELOW---------------------------------
    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer]
    "LoginMode"=dword:00000002
    -------------COPY TILL THIS---------------------------------
    STEP2: Double Click on the .REG file to change the registry. This change the Authentication Mode to enable SQL Authentication.

    STEP3: Restart the SQL Server Express Service or reboot the machine for registry change to take effect.

    Next, you need to add a user with SQLLogin. To do this: Login to SQLExpress using Express Manager and run the script below:
    -------------------------------------------
    USE MASTER
    sp_addLogin 'aspuser', 'aspuser@123'
    GO
    sp_addsrvrolemember 'aspuser', 'sysadmin'
    GO
    USE Cartridge
    EXEC sp_adduser 'aspuser'
    -------------------------------------------

    In case you want to reduce the privilege for the user then you can specify something other than sysadmin, but we can think of that once this works.

    Now change your connection string as follows:
    connectionString = "server=.\\SQLExpress;uid=aspuser;pwd=aspuser@123;database=cartridge;";

    Regards,
    Vikram

    Tuesday, July 26, 2005 2:48 PM

All replies

  • Hi,

    Your connectionstring is wrong. SQL Express supports Windows Authentication as well as SQL Server authentication. You have mixed up both in the connectionstring.

    If you are specifying the username and password then you should not set the Integrated Security to true. Remove the Integrated Security part from the connection string and it should work.

    Regards,
    Vikram
    Friday, July 22, 2005 3:14 PM
  • I did as you prescribed, but unfortunately things aren't any better, see the error below.

    I would appreciate it if you could tell me what the best practice is for the following since as you said I am confused and can't find any good documentation about this issue.

    How can I create an SQL user in SQL Express 2005 and use it with my ASP.NET application?

    How can I create a user account and authenticate to SQL Express 2005 with my ASP.NET application?

    Regards,

    Alex Ellis

    Server Error in '/print' Application.

    Login failed for user 'D3MF2Q1J\Administrator'. The user is not associated with a trusted SQL Server connection.

    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 'D3MF2Q1J\Administrator'. The user is not associated with a trusted SQL Server connection.

    Source Error:

    Line 27: connectionString = "server=.\\SQLExpress;uid=D3MF2Q1J\\Administrator;pwd=blah;database=cartridge;";
    Line 28: connection = new SqlConnection(connectionString);
    Line 29: connection.Open();
    Line 30: }
    Line 31:

    Tuesday, July 26, 2005 1:01 PM
  • Hi,

    The problem is you are using a Windows user for a SQL Authentication. Anyways I will give a step by step procedure this time.

    Enabling SQL Authentication

    STEP1:
    You need to enable SQL Authentication on the machine. This is complex in SQLExpress and you need to edit the registry. Copy the following lines of text onto a Notepad and save it with extention .REG.
    -------------COPY BELOW---------------------------------
    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer]
    "LoginMode"=dword:00000002
    -------------COPY TILL THIS---------------------------------
    STEP2: Double Click on the .REG file to change the registry. This change the Authentication Mode to enable SQL Authentication.

    STEP3: Restart the SQL Server Express Service or reboot the machine for registry change to take effect.

    Next, you need to add a user with SQLLogin. To do this: Login to SQLExpress using Express Manager and run the script below:
    -------------------------------------------
    USE MASTER
    sp_addLogin 'aspuser', 'aspuser@123'
    GO
    sp_addsrvrolemember 'aspuser', 'sysadmin'
    GO
    USE Cartridge
    EXEC sp_adduser 'aspuser'
    -------------------------------------------

    In case you want to reduce the privilege for the user then you can specify something other than sysadmin, but we can think of that once this works.

    Now change your connection string as follows:
    connectionString = "server=.\\SQLExpress;uid=aspuser;pwd=aspuser@123;database=cartridge;";

    Regards,
    Vikram

    Tuesday, July 26, 2005 2:48 PM
  • Many thanks Vikram, it was just what I was looking for.

    Regards,

    Alex
    Friday, July 29, 2005 10:45 AM
  • Thank-you Vikram I was having this problem with IIS and your solution solved my problem and made me very happy too !!!
    Sunday, August 14, 2005 12:37 AM
  • I am currently having problems with creating a new user

    this happens under visual web developer 2005
    here is what it looks like:


    User Detail
    Sign Up for Your New Account
    Password length minimum: 7. Non-alphanumeric characters required: 1.
    Sunday, November 20, 2005 7:02 AM
  • I would like to also say that i do enter a minimum of 7 char

    and also I enter one numeric char. This occurs under the Timetracker kit,
    what happens is that when the program is executed it will go into
    login page if you dont have a user and password name then
    you'll get the option of creating a new user

    Every time I enter a password the same error comes up. 
    Sunday, November 20, 2005 7:03 AM
  • It is actually asking for a password like this:

    a6B29r1!

    Notice the exlamation mark.  Any character such as this, in addition to the password combination you are already using will work just fine.

    Rule of thumb:  at least 7 characters consisting of 1 number, 1 letter and then 1 character that is not a number or letter ( like !,?,#,%,^,&).  Any of the characters appearing above your keyboard number line will do.

    Happy Databasing  Smile

    ** By the way:  You can also create a new user profile, including any other web app configurations by using the following instructions:

    In the Visual Basic IDE window, click 'Website' > 'ASP.NET Configuration'.  This will open a new IE browser with all the configuration settings for your application.

    If you want the grand tour and superb FOR FREE video instruction for any of the Visual Studio 2005 Express Editions, go to the Start Page and click on, "Video Series - Getting Started with Visual Web Developer 2005", or whatever Visual Studio Express edition you are using.

    Sunday, November 20, 2005 7:53 AM
  • Thank You very much mntlnrg

    everyhing is working find now and the videos are perfect
    I have being working with them all night

    this has being the most help tip I've got thank you very much

    once again.
    Sunday, November 20, 2005 3:54 PM
  • Hello,

    I don't find this SQLLogin or Exress Manager. (Is this not included by the SQLexpress installed bij VS2005?). If so, where can i download a working version?

    Thanks
    Tuesday, December 13, 2005 9:18 PM
  • SQL Server Management Studio Express is available for download at the Microsoft.com website.

    If the following link doesn't work, go to http://www.microsoft.com/, click "Downloads", choose "Windows Server System" as the category, and type in "SQL Express" as the query. It comes up as the second entry for me.

    Direct Link:
    http://www.microsoft.com/downloads/results.aspx?pocId=E49D77BF-D5AE-4EC6-9DFA-D7A19DBA995E&freetext=SQL%20Express&DisplayLang=en

    Thursday, January 19, 2006 3:59 PM
  • Vikram,

    Tried the reg punch, but it still isn't working.  It seems that the instance of SQLEXPRESS that was created has a problem that uninstalling/re-installing SQL Server Express doesn't clear up.  I have tried a re-install and told it to allow amix mode login, and I can't even log-in as sa.  I keep getting the error below (and the SHARED MEMORY connection is enabled)

    TITLE: Connect to Server
    ------------------------------

    Cannot connect to PAVILION-A530N\SQLExpress.

    ------------------------------
    ADDITIONAL INFORMATION:

    An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Friday, March 31, 2006 10:07 PM
  • Hi

    Vikram, ive been reading your post and it seems very helpful for enabling a connection to SQL server express. Can you or anyone else tell me where i can find the SQLLogin to add a new user? I really cant seem to find it within the SQL Server Configuration Manager?

    Thanks

    Thursday, April 6, 2006 8:03 PM
  • User instances are not supported in common language runtime (CLR) code that is running inside of SQL Server. An InvalidOperationException is thrown if Open is called on a SqlConnection that has User Instance=true in the connection string.

    The network protocol for user instances must be local Named Pipes. A user instance cannot be started on a remote instance of SQL Server, and SQL Server logins are not allowed.

    So basically to deploy your application to the real world I recomend this: First attach the .mdf file to the SQL Express back it up then restore as a new DataBase then change your sql connection string in the web.config file to disable user instance then give the ASPNET account login permission on the server and the specific database.

    Now for those of you who got the Login Controls to work but doesn't work remotely when viewed from the Internet; simply IExplore 6.0 doesn't handle the authentication ticket very well when your DNS its being "URLRedirected" or using "Frames" so just change the settings for your domain to do just the plain old "IP Address redirect to" method.

    I hope this helps someone.

    I am just a newbie trying to learn!

    Darkonekt.

    Monday, October 16, 2006 6:15 PM
  • Just to say this post help me a lot getting SQL Express working remotely using VC++ 2005. The key to it is indeed the registry file, and the use of SQL Server Management Studio Express who gives you the permission to create SQL login users.

    Many thanks to all and specially Vikram for making my day.
    Wednesday, December 6, 2006 12:13 PM
  • Hi Vikram,
    Thanks a lot for the SQL Server Express SQlAuthentication information.
    I was struggling for months to get an info on this.,
     
    Thanks
    Jose
    Thursday, January 11, 2007 6:54 PM
  • hi

     

    This is the first time I am trying to use sql express in a vb.net windows app and I cannot connect to sql express. The error says cannot find the database or authentication is failing. Can you not connect to sql express using windows authentication and what is the server parameter info. (.\\sqlexpress) ?

    Sunday, May 13, 2007 2:03 PM
  • that is quiet interesting discussion going on connection to SQL Express.

    Infact I too struggling with similar problem which helped me in cross one phase (trusted users for SQL Express) but having the following issue..

    Login failed for user 'user123'

     

    I used to have a isssue similar to below which i troubleshooted with the article..

    Login failed for user 'user123'. The user is not associated with a trusted SQL Server connection.

     

    Please let me know is there something I am missing to refer.

     

    Thanks in advance..

    Monday, July 9, 2007 11:22 AM
  • Vikram, after updating the registry, I started a new query in Sql Server Management Studio Express. I entered the following script;

     

    USE MASTER

    sp_addLogin 'John', 'Doe'

    GO

    sp_addsrvrolemember 'John', 'sysadmin'

    GO

    USE OSRADECommerce

    EXEC sp_adduser 'John'

     

    When I execute the above script, I get the following error message;

     

    Msg 102, Level 15, State 1, Line 2

    Incorrect syntax near 'sp_addLogin'.

    Msg 15007, Level 16, State 1, Procedure sp_addsrvrolemember, Line 68

    'John' is not a valid login or you do not have permission.

    Msg 15007, Level 16, State 1, Procedure sp_adduser, Line 15

    'John' is not a valid login or you do not have permission.

     

     

    Do you know what's going on?

     

    Obunike

    Thursday, July 26, 2007 12:31 AM
  • where should i save file .REG??? and what should i name the file?

    Thursday, August 2, 2007 3:44 AM
  • Easiest solution to this is addinf the folowing under system.web in web.config file (or machine.config)

    Code Snippet

    <membership>
          <providers>
            <remove name="AspNetSqlMembershipProvider"/>
            <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="LocalSqlServer" enablePasswordRetrieval="true" enablePasswordReset="true" requiresQuestionAndAnswer="false" applicationName="/" requiresUniqueEmail="true" passwordFormat="Encrypted" maxInvalidPasswordAttempts="5" passwordAttemptWindow="10" passwordStrengthRegularExpression="" minRequiredPasswordLength="4" minRequiredNonalphanumericCharacters="0"/>
          </providers>
        </membership>


    Nothe the descriprive attributes of the AspNetSqlMembershipProvider <add> tag. You can prety much set anything in regard to user registration and login validation.


    Hope this helps
    Monday, September 10, 2007 12:30 PM
  • "The problem is you are using a Windows user for a SQL Authentication. Anyways I will give a step by step procedure this time.

    Enabling SQL Authentication..."

    Thank you, Vikram, for the registry hack.  I have been chasing this problem around all evening and, after much experimentation and swearing, you provided the final link in the chain.  Someone from MS desperately needs to provide an all-in-one, fully correct FAQ page for this (in my case simply connecting to SQL Express locally using ASP), as there is much information out there on MS and non-MS sites, in help files etc. but lots of it incomplete, misleading or inapplicable.  I have been database programming professionally for the web for well over ten years but I felt like I'd regressed back that far trying to sort this out!

    Thanks.
    Tuesday, July 1, 2008 8:58 PM
  • Dear Vikram,

     

    I'm new in web programming,

    can i have a simple script to allow me to connection to database using asp.net 2.0? or any website link that have the sample for me to try?

     

    my enviroment is .net 2005 with sql server express.

     

    now i already create a database call 'test'

    user name also 'test'

     

    if i create Dropdownlist from .net and choose the datasource. it will show the data as i insert using sql statement in the database.

     

    Thanks for your help

    Best Regards

    Terence

    Monday, September 8, 2008 6:36 AM
  • Hi,
    i got the same error but while executing
    USE MASTER
    sp_addLogin 'aspuser', 'aspuser@123'
    GO

    Igot a message error
    Incorrect syntax near 'sp_addlogin'.
    thank you for your answer

    Wednesday, September 10, 2008 10:07 PM
  • Hi ,
    I am also getting the same error as below,


    Cannot open database "dbname" requested by the login. The login failed.
    Login failed for user 'SERVERNAME\ASPNET'.

    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: Cannot open database "CourseAndEventMgmt" requested by the login. The login failed.
    Login failed for user 'SERVERNAME\ASPNET'.

    Source Error:

    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

    Stack Trace:

    [SqlException (0x80131904): Cannot open database "CourseAndEventMgmt" requested by the login. The login failed.
    Login failed for user 'SERVERNAME\ASPNET'.]
    System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4844759
    System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
    System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
    System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +35
    System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +144
    System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +342
    System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +221
    System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +189
    System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +185
    System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +31
    System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +433
    System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
    System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +499
    System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +65
    System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +117
    System.Data.SqlClient.SqlConnection.Open() +122
    System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) +31
    System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +112
    System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +287
    System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +92
    System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1297
    System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e) +95
    System.Web.UI.WebControls.ListControl.PerformSelect() +34
    System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +73
    System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
    System.Web.UI.WebControls.BaseDataBoundControl.OnPreRender(EventArgs e) +22
    System.Web.UI.WebControls.ListControl.OnPreRender(EventArgs e) +18
    System.Web.UI.Control.PreRenderRecursiveInternal() +80
    System.Web.UI.Control.PreRenderRecursiveInternal() +171
    System.Web.UI.Control.PreRenderRecursiveInternal() +171
    System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +842


    Version Information: Microsoft .NET Framework Version:2.0.50727.3053; ASP.NET Version:2.0.50727.3053
    Thursday, January 8, 2009 3:55 AM
  • hOW TO Login to SQLExpress using Express Manager and run the script . I am using Sql Server 2005 express which comes with microsoft visual studio 2008.
    pls help.
    Thursday, January 8, 2009 4:03 AM
  • I'm getting the same error to when I run the query. What could be the reason? Please help!
    Wednesday, July 29, 2009 2:52 AM
  • Hi,

     I tried your answer. But its not working . I got this Error. Can you give me an answer for this.

    USE

     

    MASTER

    sp_addLogin 'aspuser', 'aspuser@123'

    GO

    sp_addsrvrolemember

     

    'aspuser', 'sysadmin'

    GO

    USE

     

    Employee

    EXEC

     

    sp_adduser 'aspuser'

    Error Message:

    Msg 102, Level 15, State 1, Line 2

    Incorrect syntax near 'sp_addLogin'.

    Msg 15007, Level 16, State 1, Procedure sp_addsrvrolemember, Line 68

    'aspuser' is not a valid login or you do not have permission.

    Msg 15007, Level 16, State 1, Procedure sp_adduser, Line 15

    'aspuser' is not a valid login or you do not have permission.

     

    Thanks & Best Regards

    Nelson

     

     

    Wednesday, June 2, 2010 12:25 PM
  • Hi,

    The problem is you are using a Windows user for a SQL Authentication. Anyways I will give a step by step procedure this time.

    Enabling SQL Authentication

    STEP1:
    You need to enable SQL Authentication on the machine. This is complex in SQLExpress and you need to edit the registry. Copy the following lines of text onto a Notepad and save it with extention .REG.
    -------------COPY BELOW---------------------------------
    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer]
    "LoginMode"=dword:00000002
    -------------COPY TILL THIS---------------------------------
    STEP2: Double Click on the .REG file to change the registry. This change the Authentication Mode to enable SQL Authentication.

    STEP3: Restart the SQL Server Express Service or reboot the machine for registry change to take effect.

    Next, you need to add a user with SQLLogin. To do this: Login to SQLExpress using Express Manager and run the script below:
    -------------------------------------------
    USE MASTER
    sp_addLogin 'aspuser', 'aspuser@123'
    GO
    sp_addsrvrolemember 'aspuser', 'sysadmin'
    GO
    USE Cartridge
    EXEC sp_adduser 'aspuser'
    -------------------------------------------

    In case you want to reduce the privilege for the user then you can specify something other than sysadmin, but we can think of that once this works.

    Now change your connection string as follows:
    connectionString = "server=.\\SQLExpress;uid=aspuser;pwd=aspuser@123;database=cartridge;";

    Regards,
    Vikram


    It's very detailed, Thanks for your effort!
    Friday, February 11, 2011 12:51 AM
  • I have the problem below, after I applied the registry and try to run the script 

    Incorrect syntax near 'sp_addLogin'.

    May I know how to fix it?

    Monday, October 31, 2011 2:35 PM