Another "Failure to access the DBMS server."

[PaulHuffman]

I have new hardware running  SQL Server 2012 R2 and I'm trying to create my first Enterprise geodatabase on it but all I can get back using the two client machines I have running Desktop 10.3.1 and SQL Native Client is "Failure to access the DBMS server"  Don't understand this because I didn't have this problem with my install at a different location a couple months ago, and I just completed the instructor led course Deploying and Maintaining a Multiuser Geodatabase earlier this week.

My DBA set up the new servers as virtual servers on the same unit.  From my DBA I got:

  NSQL 192.168.3.222

  Administrator: *********

  SQL Server instance MSSQLSERVER

  sa: *********

  NSMAP 192.168.3.223

  Administrator: ************

But when I try to create my first geodatabase, I get Failure to access the DBMS server. If the SQL Native Communications driver were missing, or wrong, I think I would get a different error message,  something about communication to the SQL server. (How does one tell if the SQL Native client is installed on a client PC? Don't see it in Data Sources.)  I wondered if Windows firewall was blocking, but I turned off public and private firewall temporarily on the client NSMAP and the SQL Server NSSQL, no change. Also tried the trick to modify the host file on the client machine to resolve the SQL host name, no change.

I pulled the Create Enterprise Geodatadata tool results into a python window to make it easier to repeat iterations with minor changes. But it also makes it easy to copy and paste my attempt as text.

>>>

arcpy.CreateEnterpriseGeodatabase_management(database_platform="SQL_Server",

instance_name="MSSQLSERVER", database_name="Orthos", account_authentication="DATABASE_AUTH", database_admin="sa", database_admin_password="**********", sde_schema="SDE_SCHEMA", gdb_admin_name="sde", gdb_admin_password="sde", tablespace_name="", authorization_file="C:/Program Files

(x86)/ESRI/License10.3/sysgen/keycodes")

Runtime error Traceback (most recent call last): File "<string>",

line 1, in <module> File "c:\program files

(x86)\arcgis\desktop10.3\arcpy\arcpy\management.py", line 4857, in

CreateEnterpriseGeodatabase raise e ExecuteError: Failure to access

the DBMS server Failed to execute (CreateEnterpriseGeodatabase).

Maybe my DBA or I have made a typo. But this has me wondering now does the db account sde need to be created first? How does the RDMS know that the sde account has a password of sde?  Or is there something wrong with my network topology.  I can't ping the SQL server from my Desktop or from the neighboring server NSMAP, but I can Remote Desktop to both NSQL and NSMAP.  

[PaulHuffman]

Yeah,  here's SSMS on the SQL machine, NSQL.  Top connection is Windows Authentication,  bottom is DB authentication using the sa account that I have been trying to use with Create Enterprise Geodatabase.  Strange thing is there is the database orthos that I was trying to create.  Did my last attempt on local host work despite the error that sde wasn't a good enough password?

[RickGeittmann]

Hmmm it is possible.

I gave up on our systems and loaded ArcMap / Catalog on the GIS server then just remote into it fire up catalog on the server and run the tools to create whatever DB I need as I was running into some of the same connection issues awhile back.

Using the above method I just create the DB in catalog and point to the key code - then set the permissions in SSMS then shell back to catalog and set any other permissions I need to.

Also use SSMS to change the parameters for the log and mdf files and other stuff as needed.

Additionally I switched and started using a named instance and all spatial DB's are built under that named instance using the DBO schema instead of the SDE schema.

You can still lock folks out of a DB in SSMS by just not mapping them to a DB in SSMS under their login or group they are assigned to.

That was my solution to trying to connect across the network with all the issues.

Afterwards all I do then is create the desk top connection to the DB in catalog on my work station and off I go.

Mayhap give the route above with loading the DK software on the server - we use a concurrent license model so no issues there.

BTW the dang windows firewall is good about blocking ports for Arc / SDE so you may talk to your network folks to check that out.

[PaulHuffman]

Now I have a Orthos and an Orthos2 database that were created with sequential runs of Create Enterprise Geodatabase while logged into NSQL,  but no sde user was created.  Looks like the schemas are dbo.   Desktop on NSQL can make a database connection to them as sa, but still no client machine can.  Wait, let me try lowering the firewall again.  

[PaulHuffman]

I keep pecking away at this.  Microsoft did a good job of hiding "local security settings" in Windows 2012 R2.  I couldn't find it under "Administrative tools" Error 15118: The password does not meet Windows policy requirements because it is not complex enough on my SQL machine but I found it by running secpol.msc How to Configure Security Policy Settings  .  I set "password must meet complexity requirements" to "disabled"  because I didn't know where to look up what the password requirements are.  Then on the SQL machine,  I was finally able use the Create Enterprise Database tool to create a new geodatabase with the schema owned by sde by using localhost as the instance.  Then on the other virtual machine running ArcServer, NSMAP, I was able to make a sa database connection to Orthos3 using instance NSQL.  But I was still unable to make a database connection at my desktop client.  Tried turning the Windows Firewall off on NSQL, tried instance NSQL, 192.168.3.222, NSQL\MSSQLSERVER, 192.168.3.222\MSSQLSERVER.  So I still have a network problem, I guess.

[RickGeittmann]

Paul,

Here is a link to the Microsoft eBook site with some free books that are helpful - also they have where you can purchase eBooks that any PDF reader will open.

https://mva.microsoft.com/ebooks

Pretty much on a server system where you are using an elevated permissions user id and password construct it so that it is at least 16 characters long.

Even when you are logged in with elevated you can access various programs as another user by holding the SHIFT key down and RIGHT CLICKING on the program and select another user or administrator level so you do not have to log in and out of a system all the time - this works also if you log in at a lower permission level you can access programs at an elevated level.

[PaulHuffman]

I thought that because I couldn't ping my SQL Server machine from my ArcDesktop client PC that I had some kind of network topology problem.  But then I found out that it was Windows Firewall policy that was blocking the ping response.  I did something like these directions for Windows 7 http://answers.microsoft.com/en-us/windows/forum/windows_7-networking/how-to-enable-ping-response-in... to get the machine echoing ping requests. Now my desktop, my ArcServer, and my SQL server all ping each other,  but I still can't make a database connection from a client machine.

[PaulHuffman]

After all that, I think I got it fixed.  I started to look into why I couldn't make a connection with SSMS either.  I found some directions that port 1433 had to be open on the SQL server machine.  I made a new firewall rule on my SQL Server machine for port 1433 incoming.  Finally client machines can create a database connection with ArcCatalog.

I don't know why taking the Windows firewall down for incoming and outgoing temporarily didn't work.  Got mislead by that test.

[PaulHuffman]

It looks like the Password Policies on these two servers were defaulted to "Minimum password length   0 characters".  I kept trying longer and longer passwords but even out at 20 characters,  I would get a "password too short" error.  When I changed this minimum password length to something other than 0,  like 8,  I could set user passwords successfully and still leave the option "Password must meet complexity requirements" enabled. 

[JohnBrockwell]

I knew it! I told you about opening port 1433 last week.

"Open the port for SQL (1433) and make sure your users have the SQL Client installed on local workstations and they will be able to consume SDE Feature Classes"

It's ok, no one listens to me. 😕

[RickGeittmann]

John - why would anyone want to do that ?????

John does bring up a good point though - we oft times setup DB's and stuff and forget to check port numbers and ensure that the needed ports are open on the firewalls with what ever firewall tool set we are using.

I have all the ESRI ports documented that things play with but forget that SQL uses quite a few ports.

I know what is going into my system documentation today...