Creating a database authentication for SQL Server 2008 Express

107 Views Asked by At

In order to have a cross-domain access to my test instance of SQL Server I need a login with a database user and not with a Windows user.

enter image description here

Once this would work I could start doing this from my *NIX environment, but basically Windows won't let me access the DB this way and throws an authentication error 28000/SQL Server Error 18456.

I already did

ALTER LOGIN [administrator] WITH DEFAULT_DATABASE=[...]
ALTER LOGIN [administrator] WITH PASSWORD=...
1

There are 1 best solutions below

1
Dean Goodman On

Verify that the SQL Server is configured to allow SQL Server auth (as opposed to Windows-only auth).

https://support.microsoft.com/en-us/kb/2121736

If that doesn't solve, check the State that accompanies the error message. If the ODBC connection error doesn't include that information, you should also be able to track the failed login attempts via the logs on the SQL Server to which you are trying to login.

https://blogs.msdn.microsoft.com/sql_protocols/2006/02/21/understanding-login-failed-error-18456-error-messages-in-sql-server-2005/

Where are SQL Server connection attempts logged?