microsoft sql express 2012 authentication connection issues - can't login

Question

I am a novice when it comes to Windows networking authentication, as well as just getting my feet wet with SQL Express, so please bear with me, hopefully this is a simple fix but so far the obvious fixes I have found in forums have not solved it.

I am trying to set up a geodatabase to be accessible by remote PC's running ESRI's ArcMap with standard license.

I have installed MS SQL Express 2012 as well as necessary components from the 2008 release to be able to access geodatabases.

I can successfully connect to the database from within ArcMap on the machine that is serving it, so I know the server is working.

However if I try and set up a connection on a remote machine I get authentication errors. I can confirm the server machine is seeing the connection attempt and the firewall is letting in the TCP/IP, as I can see in SQL Management Studio Express's log on the server machine logging the failed attempts.

I get two errors depending on how i try and connect, if I use windows authentication I get a message that the domain is untrusted so i can't use windows authentication. If I use database authentication I get a message that login failed and to check login/pwd/server. As mentioned before I know the server part seems to be correct.

In SQL Management configuration I have added a user to the DB and given it a password, I have also switched authentication over to both windows authentication and db authentication. However if I select db authentication at the connect dialog and put in the user/pwd I still get an error.

I have tried to add my remote machine's IP to the trusted domains on the server PC, but not sure I did that right. Still get errors that say the domain is untrusted if i try windows authentication.

So not sure where to turn next, or what info you folks might require to sort this out. Any leads are appreciated, thanks.

Answer 1

More information would help here and I've asked those questions in the comments. That said - a few thoughts:

Basically to access a SQL Server database you need a few things:

1. A login - this gets you into the server itself. This is not a database user, but a login at the instance level (In SSMS this is the top most security, not the security under a database.)

2. That login being mapped to a user - now there are exceptions but for the purposes here - you need that login to then be granted access to the database. This would be security found at the database level.

3. Permissions - you'll need permissions of some sort to do something in SQL.

4. SQL Server needs to have the protocol enabled that you are trying to connect with. Also if there is a firewall, the port (default is 1433 for TCP port) needs to be open.

When there are connection issues like you describe it is normally around something like not actually being on a domain but trying to use windows auth, a password mismatch, not having access to a default database selected for a login or an actual network issue like a firewall.

These are two older posts but they are fantastic resources for troubleshooting connectivity to SQL Server. This first link is from the SQL Server protocols team and describes the steps to troubleshooting connectivity issues. This second link is from the SQL Server connectivity team blog describing the various login failure messages and their states and how to troubleshoot each state.