Connecting to SQL Server Express from C#

Question

I'm trying to connect to a SQL Server database from C#, but keep get thrown by an exception:

System.Data.SqlClient.SqlExceptionwhen trying toopen()` the connection.

I have created a simple web form, with two text boxes: usernameTextBox, and passwordTextBox, and a 'sign in' button.

For now, just for testing purposes, what I'm trying to do is to insert values into a users table, when the sign in button is clicked, so this is what I have so far:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _default : System.Web.UI.Page
{
    protected void signInButton_Click(object sender, EventArgs e)
    {
        string connection_str="Data Source=.\\SQLEXPRESS;"+
        "AttachDbFilename=|DataDirectory|\\project.mdf;"+
        "Integrated Security=True;"+
        "User Instance=True";

        SqlConnection connection = new SqlConnection(connection_str);
        connection.Open();

        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.CommandText="INSERT INTO users VALUES ('"+userNameTextBox.Text+"','"+passwordTextBox.Text+"');";
        command.ExecuteNonQuery();
        connection.Close();
    }
}

As stated above, this fails at connection.Open();.

The project.mdf file is located at the VS Project folder, inside the 'App_Data' folder.

Does anyone have an idea about what can cause the Open() to fail?

I can also provide the 'exception info', if it is needed.

Thanks.

BTW, When should the connection be established? at the 'sign-in-button handler', or in the 'Page_Load' handler?

EDIT

System.Data.SqlClient.SqlException was unhandled by user code
  HResult=-2146232060
  Message=Cannot open user default database. Login failed.
Login failed for user 'J***-PC\J***'.
  Source=.Net SqlClient Data Provider
  ErrorCode=-2146232060
  Class=11
  LineNumber=65536
  Number=4064
  Procedure=""
  Server=\\.\pipe\8EDE82F8-E3AA-40\tsql\query
  State=1
  StackTrace:
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
       at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
       at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
       at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
       at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
       at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
       at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
       at System.Data.SqlClient.SqlConnection.Open()
       at _default.signInButton_Click(Object sender, EventArgs e) in c:\Users\J***\Documents\Visual Studio 2012\Projects\db5774\default.aspx.cs:line 23
       at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
       at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
  InnerException:

"keep get thrown by an exception" - well what *exactly* does the exception look like? Please show the full stack trace including the message. — Jon Skeet, May 18 '14 at 07:54
By the way: Never use user input to build sql queries with string concatenation. Your code is vulnerable to sql injection. You should use parameterized sql queries as shown at the msdn library page on SqlCommand.Parameters property. — Andreas, May 18 '14 at 08:25

Patrick Hofman · Answer 1 · 2014-05-18T08:12:49.000

1

So the exception says:

Message=Cannot open user default database. Login failed. Login failed for user 'J***-PC\J***'.

That would mean the user has no access to the default database, usually master.

Alter the user so it can access the default database, or better, point the default to another database.

Use this sql for that:

alter login [username] with default_database = database_that_user_can_access

Or change the connection string by adding this:

Database=database_that_user_can_access

edited May 18 '14 at 08:12

answered May 18 '14 at 08:02

Patrick Hofman

153,850
22
249
325

Hi Patrick. So what exactly should be changed? How can I give my user the right permissions to access the default database? changing the connection string? And where should this: `alter login [username] with default_database = database_that_user_can_access` be typed? – so.very.tired May 18 '14 at 08:10
You have to either change your database (granting persmission, use the statement I gave), or the connection string. I will update the answer. – Patrick Hofman May 18 '14 at 08:12
@so.very.tired: Any luck? Need more help? – Patrick Hofman May 19 '14 at 06:00
No. :( It keeps giving me this annoying exception, I tried to connect to SQL directly through the 'Sql Data manager' and it also fails with some weird uninformative error, so I decided to remove SQL server, and any software remotely related to the SQL server and to reinstall the damn thing. I'll post if it helped... Thank you so much for the help/ – so.very.tired May 19 '14 at 14:17

score 0 · Answer 2 · answered May 18 '14 at 08:23

0

Try this:

  AttachDbFilename=~\..\ProjectName\App_Data\project.mdf

answered May 18 '14 at 08:23

Meysam Tolouee

569
3
17

2

The syntax used is correct and the preferred standard. – Patrick Hofman May 18 '14 at 08:25

Connecting to SQL Server Express from C#

2 Answers2