ArcGIS Desktop: can't connect to MSSQL via database authentication

[NilsNolde]

Hi,

first, the specs:

ArcGIS Desktop 10.6

MSSQL 2016

MSSQL client v13 installed

I created database users a while before upgrading to ArcGIS 10.6 (from 10.4). Now, connecting remotely (and thus not being able to use OS authentication), I realized I can't connect via the database users. It tells me 'bad login'. However, I checked that they are properly set up, which seems to be the case (to me).

I also read about enabling all services that MSSQL should run. So, I headed over to the SQL Server Configuration Manager and indeed, 'SQL Server Browser' and '.. Agent' were not running. I started them but realized smth weird in 'SQL Server Browser' properties:

- I have no idea which 'account' to specify. If 'Built-in Account' or a domain account. Currently it's 'NT Authority\Network Service'

- Also there's no 'SQL Service Type' specified, which seems to be the case for the other running services.

Can anyone give me a hint what I could try?

Many thanks

Nils

[KevinDunlop]

It sounds like it could be a few things.

1.  Wrong username/password

2. The database account is disable/locked.  It could also be that it is not granted permissions to connect to that SQL Server instance.

3. The database was created on one SQL Instance but was moved to another one.  User accounts need to be created instance level, then assigned to the database.  If moved the database to a different instance (like an always on failover or restoring a back up), the database level accounts might not be in the instance.

4. SQL Server Instance was changed to use only Windows Authentication mode.

What your image shows is the account running the SQL Server software.  I would not change this if possible.  

[RexRobichaux2 · Accepted solution]

Hi Nils,

   There could be a variety of culprits causing this issue for you, unfortunately. Just to clarify- can you connect with either database authenticated users OR OS authenticated users or do both (and therefore all) connections fails to this instance from ArcGIS clients? A couple of initial things to check in the database are:

• Ensure you have mapped logins to the applicable databases in SQL. Right click the login < Properties < User mapping < ensure the applicable database is checked for the user.
• If OS users succeed and database authenticated users (only) fail- check the Server authentication setting under the server properties (right click instance < Properties < Security page)- ensure SQL Server and Windows Authentication mode is selected.
  • 
• Additionally, under SQL Configuration Manager from the screenshots you provided select Protocols for MSSQLSERVER. Ensure Named pipes and TCP/IP is set to Enabled as below:
  • 
• One nice trick to see if the SQL instance is accessible from your remote workstation at all is to (on a client machine with ArcGIS), create a new text file on the desktop. Once created rename to test.udl and rename the file. This will create a data link file capable of testing connectivity to SQL servers within your domain.
  • Open the new .udl file, and hit the drop down for server name- this will reach out through your available domain to find available instances of SQL. 
    • If your instance doesn't show up- you have a network, DNS, or firewall issue
  • If your instance does show up, test connecting outside of ArcGIS by entering a username and password that exist as a mapped login / database user in the database. 
  • Select the intentended database if more than one exists on the instance.
  • 
  • Test connection- this will utilize the MS SQL ODBC 13 driver and test a connection taking ArcGIS out. If it fails, the issue is with configuration or server availability. If it succeeds we need to determine why ArcGIS is unable to communicate through the client.

I hope this is helpful!

Best,

Rex

[NilsNolde]

Rex, you saved my ***! Thanks sooo much!

So, I tried your 4th advice, creating a .udl file and follow along. Although my SQL instance didn't auto-show in the drop-down, typing the server domain name did the trick too. When I entered the database user login in question, it raised an error, that it couldn't read the default database of the user. So, back to SQL Manager, I set the default database ( right-click on user login: Properties > General > ..) and it worked! Now, ArcGIS is also recognizing it. Wouldn't eeeever have thought about that!!

The lesson learned here is: the errors the 'raw' database connector throws should be piped directly to ArcGIS error messages in the DB connector. It would've saved me a full 3 hours of trial/error if it told me it couldn't connect to the default database for the user. And I don't think I ever read about that problem in any tutorial or forum.. Could you please pass that on Rex?

Many thanks

Nils

[RexRobichaux2]

Hi Nils, 

 Very glad to hear that the 4th approach lead you to the issue! I must agree- some error messages could be much more descriptive. I'll do my best to pass it on and hope this approach may assist you should you ever encounter additional connection issues to SQL Server!

Best,

Rex R