Database login failed for user

1

I am aware that this question repeats from time to time but I tried almost every response here and I still encounter a problem with code below that tries to connect to my MS SQL Server 2014:

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "(LocalDB)\\v11.0";
builder.UserID = "LOGIN";
builder.Password = "PASSWORD";
builder.InitialCatalog = "DATABASE";
builder.IntegratedSecurity = false;
SqlConnection connection = new SqlConnection(builder.ConnectionString);
connection.Open();

I created an user LOGIN with password PASSWORD:

CREATE LOGIN [LOGIN] WITH
PASSWORD=N'PASSWORD',
DEFAULT_DATABASE=[DATABASE],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=ON

and enable account.

alter login LOGIN enable

I checked if in my Microsoft SQL Server 2014 Management Studio user LOGIN is in Security folder and in Database Secuirty Users folder also contains User LOGIN.

I tried also using Windows Authentication in my Visual Studio to connect to database and I succeeded. However I cannot repeat this connection process with my code above even when I try to execute the code below:

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "(LocalDB)\\v11.0";
builder.InitialCatalog = "DATABASE";
builder.IntegratedSecurity = true;
SqlConnection connection = new SqlConnection(builder.ConnectionString);
connection.Open();

I receive following error:

System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'LOGIN'.
    in System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
    in System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
    in System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
    in System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
    in System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
    in System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
    in System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
    in System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
    in System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
    in System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
    in System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
    in System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
    in System.Data.SqlClient.SqlConnection.Open()

This database is new on my server; I made a new installation yesterday and I used default installation settings as far as I remember.

c#
sql-server
database
asked on Stack Overflow Dec 28, 2018 by Piotr Wojcik • edited Dec 28, 2018 by Tab Alleman

2 Answers

0

There are few types of authentications in SQL Server like windows, server etc.

The login you created via SQL will try to login via SQL Server authentication mode. So make sure your SQL Server allows use that type of the authentication.

You can check you SQL server settings:

Right click on server name -> Properties -> Security:

Mixed mode authentication should allow both windows and server:

enter image description here

answered on Stack Overflow Dec 28, 2018 by Eugenio R • edited Dec 28, 2018 by Alejandro
0

I would like to thank you for your answers. Solution is in code below:

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = ".";            // DataSource changed             
builder.InitialCatalog = "DATABASE";
builder.IntegratedSecurity = true;
SqlConnection connection = new SqlConnection(builder.ConnectionString);
connection.Open();
answered on Stack Overflow Dec 28, 2018 by Piotr Wojcik • edited Dec 28, 2018 by Piotr Wojcik

User contributions licensed under CC BY-SA 3.0