Bad login problem with database connection in 10.5.1

[K_Rocks]

Data is currently stored in several geodatabases in SQL Server 2008 R2, originally created using desktop 9.3 and accessed through SDE. There were 2 users set up in order to connect to the geodatabases - a Read Only (RO) user and a user which could edit. The RO user has Connect permission only in SQL Server; the Edit user has Connect, Create function, Create procedure, Create table and Create view permissions. We are currently on 10.3.1 and still accessing the same geodatabases on the same server. However, we need to migrate these geodatabases to SQL Server 2016 on a new server. That has been done by our in-house SQL expert, with the exact same configuration and users privileges. Both servers lie in the internal domain.

In order to access these in SQL Server 2016, we need to upgrade our ArcGIS desktop installs to 10.5.1. This has been done on a 64-bit Windows 7 pc to test the new server connection using ArcCatalog. There was immediately a problem connecting to the database which mentioned Microsoft ODBC Driver 13 for SQL Server, so I have installed that.

Consequently, accessing the geodatabases works fine under the RO user; the Connection Properties in ArcCatalog can be changed to point to the new server or add a new database connection with the new server and all connects as normal.

However, when the same is attempted with the Edit user, error warnings appear.

When a connection is already set up for a RO user, switching the user to the Edit user gives a "Failed to connect to specified server. Do you want to continue? Bad login user" error.  Clicking Yes allows the connection to proceed and the dataset can be accessed and used, but only up until the connection falls asleep. When you try to reconnect, it gives a "Failed to connect to database. Bad login user" error and does not allow access.

When attempting to Add Database Connection afresh using the Edit user, it also produces a "Failed to connect to database. Bad login user" error and does not allow access.

The original SQL Server 2008 geodatabases cannot be accessed in 10.5.1 with ODBC Driver 13 installed.

Our SQL expert maintains there is absolutely no difference between the way the Edit user was set up in the previous SQL Server 2008 and the new 2016 one.

Any ideas what the issue might be?

Thanks in advance.

https://community.esri.com/groups/geodatabase?sr=search&searchId=717d5bfb-9af1-4ad9-af7c-e21ca81f48f...‌ https://community.esri.com/community/gis/enterprise-gis?sr=search&searchId=617af519-3792-4a85-9567-9...‌

[Asrujit_SenGupta]

In case, the editor user is a Database Authenticated login, was a Resynch done after restoring the database on the SQL Server 2016 instance?

How To: Re-synch SQL Server logins or users after restoring a database from backup 

[ScottFierro2]

Agree with asrujit_pb‌ about checking to be sure it's not a synch issue that's left orphans.

Any specific reason that you are using the ODBC 13 driver?

If not then I'd try the Native Client. Believe ESRI has a copy of it available for download with the 10.5.1 release but if not you can go out and download it from MS site. Just remember it's the 2012 Native Client because there's been no new release for it in the 2014 or 2016 SQL releases. We've been using the Native Client since 2008 R2 agency wide for a few hundred users and not had any issues yet.

[Asrujit_SenGupta]

SNAC, or SQL Server Native Client, is a term that has been used interchangeably to refer to ODBC and OLE DB drivers for SQL Server.

SQL Server Native Client | Microsoft Docs 

The client software\Drivers part is fine. It is just a change in terminology. The new releases are called ODBC drivers.

Download Microsoft® ODBC Driver 13 for SQL Server® - Windows + Linux from Official Microsoft Downloa... 

[K_Rocks]

Thanks for the responses. I installed ODBC Driver 13 as an error mentioned it when I tried to connect to the server at the beginning, so any Native Client that was included in the desktop 10.5.1 install did not seem to be enough to talk to SQL Server 2016.

The Resynch has been done but the issue remains the same. We also tried running it from a different pc, on the off-chance that it was some Windows error on the pc being used, but the same problem occurs. We also tried creating another 'sdenew' user with the same permissions as the 'sde' user in order to see if it could connect properly. Whereas it appears to initially and expands to show feature datasets, it does not show any feature classes or rasters.

Is it possible some sde user conflict arises when copying a geodatabase from SQL Server 2008 to 2016? Or is a further step needed within ArcCatalog to define the geodatabase administrator anew?

Thanks

[Asrujit_SenGupta]

We also tried creating another 'sdenew' user with the same permissions as the 'sde' user in order to see if it could connect properly. Whereas it appears to initially and expands to show feature datasets, it does not show any feature classes or rasters.

That could be because this new login doesn't have permissions to see the data. Just granting 'SELECT' from the database end to the login, should suffice in that case.

I still feel its something to do with the permissions during the migration. Check the Database properties, to make sure that the properties show correctly for SDE

[ScottFierro2]

So here's another thought when we handle a brand new SQL DB that you are configuring for the first time we have a 2 step process we refer to as the "is DBO then as DBO switch". Essentially, when we build the blank DB on the SQL side we make the sde user the dbo with the default schema set to dbo, we run the ESRI processes to make it an eGDB, after that process is done we change the sde users permissions from being the dbo and give dbo back to the SQL admin user but set the sde user as db_owner role.

https://serverfault.com/questions/247060/sql-server-dbo-vs-db-owner-role-member

May just need to get the sde user set back to be the dbo. Additionally, don't be surprised if this occurs although I have seen it happen more with Oracle DB's but when you get a successful connection with the sde user you may get a prompt to provide the key code file from your ArcServer install as part of this. We generally upgrade our SQL side stuff at same pace as our ESRI platforms so not seen it there but our Oracle DB's lag behind as they are still 11g so 1 sde schema for the whole DB makes things lag for ensuring all users/data sets are ready to handle new version shifts and it happens in Oracle often for us.

[Asrujit_SenGupta]

Also check this out:

ArcGIS 10.5.1 (Desktop, Engine, Server) SQL Server Permissions Patch 

More details regarding a known Bug are present in the below post:

https://community.esri.com/thread/198170-arcgis-1051-arcgis-pro-2-bug-connecting-to-sql-server 

This may not be the issue in your case, but no harm checking it and trying the patch...