Login failed Error: 18456

  • scott clemons

    Mr or Mrs. 500

    Points: 550

    I have read countless articles on the internet about fixing this error, but nothing works. Sorry, if there is an easy answer, but I can't figure it out.

    Installed from CD SQL Server 2014 Dev Ed on my laptop. Setup as named instance with mixed mode authentication.

    When trying to connect to server in SSMS 2014 using Windows Authentication or SQL Server Authentication, I get this error. (Login failed for suer 'xx' (Microsoft SQL Server, Error: 18456)

    The server name is listed in the drop down, so that should be correct.

    The name listed for Windows Authentication is the name I selected as Administrator when installing SQL Server.

    Network Configuration Protocols: Shared Memory(1), TCP/IP(2), Named Pipes(3) - All Enabled. It doesn't seem to matter which are enabled/disabled or in what order. All combinations result in the same error.

    The user I selected as Administrator in the installation is a domain user, so it appears as <domain>\<user>. Is a domain user prevented from using the Developer Edition? If so, how would you switch to a local Administrator to use with Windows Authentication. The User name is greyed out.

    I tried installing SQL 2014 Dev Ed CD on 3 different machines. It works on a laptop that is a member of WORKGROUP, but failed on two machines that were members of the same domain . That is a commonality, but is that the issue?

    I would think you could connect with the "sa" account. That gives the same error. You can type in the password correctly or just make something up. Both give the same error.

    Really stumped here.

    Thx

    CSC

  • Eirikur Eiriksson

    SSC Guru

    Points: 182509

    scott clemons (3/20/2016)


    I have read countless articles on the internet about fixing this error, but nothing works. Sorry, if there is an easy answer, but I can't figure it out.

    Installed from CD SQL Server 2014 Dev Ed on my laptop. Setup as named instance with mixed mode authentication.

    When trying to connect to server in SSMS 2014 using Windows Authentication or SQL Server Authentication, I get this error. (Login failed for suer 'xx' (Microsoft SQL Server, Error: 18456)

    The server name is listed in the drop down, so that should be correct.

    The name listed for Windows Authentication is the name I selected as Administrator when installing SQL Server.

    Network Configuration Protocols: Shared Memory(1), TCP/IP(2), Named Pipes(3) - All Enabled. It doesn't seem to matter which are enabled/disabled or in what order. All combinations result in the same error.

    The user I selected as Administrator in the installation is a domain user, so it appears as <domain>\<user>. Is a domain user prevented from using the Developer Edition? If so, how would you switch to a local Administrator to use with Windows Authentication. The User name is greyed out.

    I tried installing SQL 2014 Dev Ed CD on 3 different machines. It works on a laptop that is a member of WORKGROUP, but failed on two machines that were members of the same domain . That is a commonality, but is that the issue?

    I would think you could connect with the "sa" account. That gives the same error. You can type in the password correctly or just make something up. Both give the same error.

    Really stumped here.

    Thx

    CSC

    Can you post the full error message, in particular what are the values for Msg, Level, State and Server?

    😎

  • VastSQL

    SSC-Dedicated

    Points: 35906

    Try connecting using SSMS as run as administrator.

    Hope the domain account you mentioned is login in SQL Server.

    Post the full error .

  • scott clemons

    Mr or Mrs. 500

    Points: 550

    First time posting this type of info. Wasn't sure if I should provide the computer, domain or user specific info, so I replaced with <#####>. Hope this helps.

    Same error for both the domain user and sa. Error, Severity, State and Line number identical.

    CSC

    ===================================

    Cannot connect to <#####>\SQL2014_DEV.

    ===================================

    Login failed for user '<domain>\scott'. (.Net SqlClient Data Provider)

    ------------------------------

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

    ------------------------------

    Server Name: <#####>\SQL2014_DEV

    Error Number: 18456

    Severity: 14

    State: 1

    Line Number: 65536

    ------------------------------

    Program Location:

    at 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)

    at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)

    at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)

    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 Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)

    at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

  • Lowell

    SSC Guru

    Points: 323463

    it's very easy to zip through the install clicking next next next, but forgetting to add AD login as an administrator on the SQL instance.

    you probably need to add yourself as sysadmin again;

    this user had the exact same issue today,

    so download the script mentioned in that post, and run it.

    your error shows your instance is named SQL2014_DEV, so that is the value to type in the command prompt when it asks for which instance to add you to.

    http://www.sqlservercentral.com/Forums/FindPost1771000.aspx

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Eirikur Eiriksson

    SSC Guru

    Points: 182509

    scott clemons (3/21/2016)


    First time posting this type of info. Wasn't sure if I should provide the computer, domain or user specific info, so I replaced with <#####>. Hope this helps.

    Same error for both the domain user and sa. Error, Severity, State and Line number identical.

    CSC

    ===================================

    Cannot connect to <#####>\SQL2014_DEV.

    ===================================

    Login failed for user '<domain>\scott'. (.Net SqlClient Data Provider)

    ------------------------------

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

    ------------------------------

    Server Name: <#####>\SQL2014_DEV

    Error Number: 18456

    Severity: 14

    State: 1

    Line Number: 65536

    If I recall correctly the Severity: 14 & State: 1 means that the account is disabled, do you have an 18470 in the error log?

    😎

  • scott clemons

    Mr or Mrs. 500

    Points: 550

    Wow! That appears to have worked. I ran the script the 1st time and it didn't prompt for the Instance, just ran with the default Instance. Ran is again and added my Instance name as an option on the command line. It worked.

    As I final thought, you select the mode of authentication and Administrator during the Database Engine Configuration step of the installation. In Server Configuration I selected Mixed mode and supplied a password for sa. In the Specify SQL Server Administrator section I clicked the "Add Current User" button. The domain user in question appeared in the users area.

    Wonder why that step didn't add the user to sysadmin?

    Thanks for the help. On the the next problem:)

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply