Failure to Access the DBMS Server when making DB connection - I can access it through /SQL Management Studio just fine

7730
7
06-19-2017 11:15 AM
MKF62
by
Occasional Contributor III

We have found the solution. Our ODBC driver was out of date. When using 10.4 or 10.5, the you must have ODBC Driver 13. We had ODBC Driver 11. By downloading version 17.1 of SSMS (the latest version), the ODBC driver was updated and in turn, it allowed me to make a database connection to our SQL databases through ArcMap/Catalog.

We have an SQL Server (2014) with a bunch of databases on it, created by the previous data analyst. He set up this entire system and I don't know a whole lot about how it's configured. The problem is when I try to make a Database Connection in ArcMap/Catalog, I get the error message "Failed to connect to the specified server. Failure to access the DBMS server." Sometimes I also get "Unable to connect to database server to retrieve database list; please verify your server anme, user name, and password info and try again. Failure to access the DBMS server." I have tried it using both my windows authentication as well as the database authentication using the usernames/passwords left behind. None of it works. The weird thing is that I can connect to this server with no problems at all using SQL Server Management Studio, it's just a problem when I try to connect to these databases through ArcMap or Catalog. 

We've tried everything we can think of, specifying port numbers (1443 [the port of our SQL server], 6080, 6443), making sure my IP is allowed through the firewall that's setup, and re-registering my machine with the IT division around here so they're not blocking anything. I can see no reason I can't connect to this instance.

We also tried enabling SQL browser services and making sure that is running. Doesn't seem to make a difference. Remote connections are allowed to the server. Additionally, my co-worker can make a database connection from his machine successfully using either authentication type (windows authentication with his credentials), but he cannot make the same connection on my machine using his credentials or the database usernames/passwords. Anybody have any ideas? I'm a real beginner when it comes to database connections and servers, so if you suggest I try something, please be detailed!

https://community.esri.com/community/gis/managing-data?sr=search&searchId=ceaad849-1129-431c-8b23-e7...

7 Replies
JoeBorgione
MVP Emeritus

Do you have the (proper) SQL Server Native Client installed on your machine? (See: Connect to SQL Server from ArcGIS—Help | ArcGIS Desktop )

That should just about do it....
MKF62
by
Occasional Contributor III

Yes. I have SQL Server Native Client 11.0

0 Kudos
JoeBorgione
MVP Emeritus

That's a head-scratcher.  Your co-worker can get to it from his machine, but his credentials fail on your machine.  Do your credentials work on his machine?  Do you have any other computers with ArcGIS installed that you can try?  Just trying to isolate the problem to your computer.  So weird that you can connect via SSMS.  What if you create a new login for yourself?  I'm just kind of pulling at straws here....

It's seems odd to me that both windows and database authentication are set up.  I always thought it was one or the other; I've always used database authentication as my clients are connecting from other networks, outside my physical location.

That should just about do it....
0 Kudos
MKF62
by
Occasional Contributor III

While I have yet to try my credentials on another machine, I only really need one type of authentication to work and we know the username/password combinations for specific databases does work on other machines. I think it has to be a network problem with my specific computer (which is why we went through the re-registration of my machine to make sure IT wasn't blocking it and double-checked that it's getting through the firewall). We even turned the firewall off altogether to see if that would fix it, but it didn't. 

We use our windows authentication to get into the SSMS, but then made additional logins for access to databases in a specific manner (read only, read & write, etc.) - so if I want to connect to a database in a read-only manner, I could use the specific login info with database authentication to access it on our server (this is my limited understanding anyway).

I might try creating a new login, but I mostly don't want to mess with too much right now as I'm brand new to this job and don't have a good grasp on how they've got this thing setup... don't want to break anything  

0 Kudos
ChristopherMcClain
Occasional Contributor II

what versions of SDE and Desktop are you using is it possible the desktop is newer then the sever software?

0 Kudos
MKF62
by
Occasional Contributor III

We've tried it on 10.4 and 10.5. I'll have to figure out where to find the SDE info, is there an easy way to check that? Sorry for the noob question. 

0 Kudos
JamesCrandall
MVP Frequent Contributor

Just a quick google search found this ESRI support item but not sure if it's relevant --- check to see if the user account is in the db_denydatareader role in any database.