Link to home
Start Free TrialLog in
Avatar of jspc
jspcFlag for Australia

asked on

SQL Connection (Error 18456)

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?
Avatar of Cody Smith
Cody Smith
Flag of United States of America image

When you had the successful connection, are you running the software from the SQL Server and connecting to localhost?
Also,

Have you given users any rights or roles on the SQL Server under Security?
Avatar of jspc

ASKER

Yes running the software locally on the SQL Server.

No as isn't this handled via Windows Authentication?
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.
Avatar of jspc

ASKER

I have tried that - still same issue
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.
Avatar of jspc

ASKER

Yes have also tested via ODBC and get the same error number
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.
Avatar of jspc

ASKER

Yes I did restore from SQL Server 2008 R2 > SQL Server 2014.
Do you think that is the issue?  What is Autofix?
ASKER CERTIFIED SOLUTION
Avatar of Cody Smith
Cody Smith
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jspc

ASKER

Ok I'll try - thanks
I wouldn't advise adding users as admins.   DBO of a database maybe, but not admin.
You can provide access based on your need. !!
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