Not sure what I am doing wrong so I have included the parameters I used which I used the existing login and this article for reference: http://itproguru.com/expert/2014/09/how-to-fix-login-failed-for-user-microsoft-sql-server-error-18456-step-by-step-add-sql-administrator-to-sql-management-studio/ Whatever I do I get the Error: 18456 message.
Ok found the answer after an hour of trying various options.
Server properties - Security (see image)
Shame there is no warning message somewhere when you create SQL Authentication users that they have no access.
-
There is a clear message in the error log: Date xxx Log SQL Server (Current ) Source Logon Message Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>] – Spörri Oct 13 '16 at 23:53