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.