Solved

SQL Connection (Error 18456)

Posted on 2016-11-14
14
91 Views
Last Modified: 2016-11-15
Hello, we have installed a new install of SQL Server 2014 Enterprise Edition on our new Server and enabled TCP/IP under the Configuration Manager with a restart.

If we try to connect to our database via our accounting software logged on as the Windows Administrator, we have a successful connection. If however, one of our other PC’s (Users) tries to connect it fails with an 18456 SQL Error.
Login Failed for User (Microsoft SQL Server, Error: 18456)

We are using mixed authentication mode (Windows Authentication) and out Firewall is disabled now.
Would anyone be able to assist us?
0
Comment
Question by:jspc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
14 Comments
 
LVL 1

Expert Comment

by:Cody Smith
ID: 41887229
When you had the successful connection, are you running the software from the SQL Server and connecting to localhost?
0
 
LVL 1

Expert Comment

by:Cody Smith
ID: 41887235
Also,

Have you given users any rights or roles on the SQL Server under Security?
0
 

Author Comment

by:jspc
ID: 41887241
Yes running the software locally on the SQL Server.

No as isn't this handled via Windows Authentication?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Expert Comment

by:Cody Smith
ID: 41887245
Go Under security - Login, right click and select new user.  Select Windows Authentication and type the login name as DOMAIN\USERNAME.

On the left select "User Mapping", Check the box next to the database and select the appropriate Database access for that user.
0
 

Author Comment

by:jspc
ID: 41887248
I have tried that - still same issue
0
 
LVL 1

Expert Comment

by:Cody Smith
ID: 41887251
If you look under Security beneath the database, do you see the user listed?

Have you tried using the ODBC Connection in windows to test the connection? I like to do this to remove the application from the equation.
0
 

Author Comment

by:jspc
ID: 41887255
Yes have also tested via ODBC and get the same error number
0
 
LVL 1

Expert Comment

by:Cody Smith
ID: 41887261
Did you restore this database from another SQL server?  If so, and this user was in the security group, you may need to run AUTOFIX for that username.
0
 

Author Comment

by:jspc
ID: 41887262
Yes I did restore from SQL Server 2008 R2 > SQL Server 2014.
Do you think that is the issue?  What is Autofix?
0
 
LVL 1

Accepted Solution

by:
Cody Smith earned 500 total points
ID: 41887265
Try this on the database

EXEC sp_change_users_login 'Auto_Fix', 'user'

Just change user to the username of the user.
0
 

Author Comment

by:jspc
ID: 41887274
Ok I'll try - thanks
0
 
LVL 1

Expert Comment

by:Cody Smith
ID: 41887280
I wouldn't advise adding users as admins.   DBO of a database maybe, but not admin.
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41887281
You can provide access based on your need. !!
0
 
LVL 4

Expert Comment

by:Daniel Jones
ID: 41887333
First see the details of the error message.
The error message comes with the "state"
like
Error 18456, Security: 14, State: 8.
or
Error 18456, Security: 14, State: 2

The solution for every state is different

Take a look at this link http://www.sqlmdfviewer.org/fix-sql-error18456.html it will show you the reasons and workaround to every SQL server error 18456 states
0

Featured Post

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question