Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 164
  • Last Modified:

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?
0
jspc
Asked:
jspc
1 Solution
 
Cody SmithOwnerCommented:
When you had the successful connection, are you running the software from the SQL Server and connecting to localhost?
0
 
Cody SmithOwnerCommented:
Also,

Have you given users any rights or roles on the SQL Server under Security?
0
 
jspcAuthor Commented:
Yes running the software locally on the SQL Server.

No as isn't this handled via Windows Authentication?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Cody SmithOwnerCommented:
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
 
jspcAuthor Commented:
I have tried that - still same issue
0
 
Cody SmithOwnerCommented:
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
 
jspcAuthor Commented:
Yes have also tested via ODBC and get the same error number
0
 
Cody SmithOwnerCommented:
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
 
jspcAuthor Commented:
Yes I did restore from SQL Server 2008 R2 > SQL Server 2014.
Do you think that is the issue?  What is Autofix?
0
 
Cody SmithOwnerCommented:
Try this on the database

EXEC sp_change_users_login 'Auto_Fix', 'user'

Just change user to the username of the user.
0
 
jspcAuthor Commented:
Ok I'll try - thanks
0
 
Cody SmithOwnerCommented:
I wouldn't advise adding users as admins.   DBO of a database maybe, but not admin.
0
 
Pawan KumarDatabase ExpertCommented:
You can provide access based on your need. !!
0
 
Daniel JonesData Research AnalystCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now