Helpful Tips: Permissions and Addressing

 

If you get the error " Failure in Addressing module; exit code was 1" This is most likely a permissions error

The reason this is happening (while all other matching related SP's may work) is because the addressing runs outside of SQL Server. The reason it runs outside of SQL Server is so that if it crashes, it doesn't bring the SQL instance down with it, so it runs under the context of the matchIT SQL Service.

When you first install mSQL windows will set the service to run as the user with the lowest set of permissions by default - normally 'local service', which only exists on that machine.

Recommendations

Option 1: Allow it to work with both local and remote instances by setting the matchIT SQL Service to run as a domain level user

Change the matchIT SQL Service to run as a domain user instead of the computer’s Local Service account.  Then create a login for this account within the SQL Server instance.  Lastly, for each database within the SQL Server instance that the address correction will need access to, create a user for the login and give it the ‘db_datareader’, ‘db_datawriter’, and ‘db_ddladmin’ database roles.

The matchIT SQL Service can be configured from a command console as follows:

cd /d “C:\Program Files\matchIT SQL\bin”
matchITSQL /stop /uninstallmatchITSQL /install:

matchITSQL /install:domain\username:password /start

If you are uninstalling then reinstalling the software or installing elsewhere, the matchIT SQL service will be recreated using the default Local Service account.  So you would need to rerun the above steps after each reinstallation or upgrade (a batch script, in a secure folder, could be created for this).

If you're upgrading, When installation starts, we simply stop the service, we don’t delete it.  When installation ends, we then restart the service.  It’ll use the credentials it was last configured for, it won’t revert to the default Local Service account.  you simply would run the full installer and it will update files with the newer version, only one version of matchIT SQL can exist on a machine at a time.

Option 2: Only works with local instances

Additional configuration steps must be performed before the addressing stored procedure can be used (the following steps show how to configure addressing with the example database, matchIT_SQL_demo).

Firstly, the built-in Local Service account must be given write access to the database.  This is because the address correction actually occurs outside the stored procedure; it is run under the context of the matchIT SQL Service which, by default, uses the Local Service account (a low privilege Windows service account).

Secondly, the account must be given the following roles for the matchIT_SQL_demo database: db_datareader, db_datawriter, and db_ddladmin.  Alternatively, the account can be given the db_owner role instead of these three roles.

Running in Management Studio

Here are the steps that should be run in the Management Studio:

USE [master]
GO
CREATE LOGIN [LocalService] FROM WINDOWS
GO
USE [matchIT_SQL_demo]
GO
CREATE USER [LocalService] WITH DEFAULT_SCHEMA=dbo
EXEC sp_addrolemember ‘db_datareader’, ‘LocalService’
EXEC sp_addrolemember ‘db_datawriter’, ‘LocalService’
EXEC sp_addrolemember ‘db_ddladmin’, ‘LocalService’

If the matchIT SQL service isn’t running as the Local Service account (as per the above recommendations), use the account name (i.e. domain\username) in place of LocalService.