When registering a SQL Server database connection in ArcGIS Server the following error appears:
“The connection property set was missing a required property or the property value was unrecognized.”
“The connection property set was missing a required property or the property value was unrecognized. Connection was attempted with an older version of SQL Server client communications software that is not compatible with the SQL Server database server.”
It is known that the SQL Server client communications software is up to date and compatible with the SQL Server database server.
Note: This solution is relevant if your database connection uses operating system authentication. This solution does not apply if your database connection uses database authentication.
The ArcGIS Server Account should be added as a login in SQL Server for the database that is being registered. The ArcGIS Server Account user may be a local operating system user on the server in question or a domain user. An example of the name for the ArcGIS Server operating system user when it is a local operating system user is SERVER_NAME\arcgis.
To determine the name of the ArcGIS Server user in Windows, search for View Local Services, find the service called “ArcGIS Server” and check the name of the user under the column “Log On As”. You may see a user specified in the following type of format:
The . in “.\arcgis” in this example is the name of the local server, so when adding this login to SQL Server specify the full name of the server, for example, SERVER_NAME\arcgis.
In SQL Server Management Studio go to Security > Logins > Right-click > New Login > General > Create a login with the desired name.
In the Login Properties, go to User Mapping and map the login to the database in question. The User should be set to be “dbo” and the Default Schema should be set to be “dbo”.
Under “Database role membership” click on db_datareader and db_datawriter (in addition to public).
It is essential to enable these roles as the associated permissions with these roles will add functionality such as selecting, querying, connecting, inserting rows, and other essential database operations for server functionality.
After the ArcGIS Server Account is given access to the database, you will be able to register the database connection with ArcGIS Server.