Introduction
Connecting Alert to an SQL database is used to export databases or from a message processor. It is therefore necessary to perform configurations on the machine where Alert is installed and on SQL server. To simplify the configuration, it is advisable to use a UDL file to test the connection. Once the connection is made via the UDL, the link from Alert is carefree.
Configuring SQL Server
Creating a Database for Alert
In order to connect Alert to a database, a database must be created before.
To create this database, we will use SQL Server Management Studio (SSMS).
After having install SSMS, you can start it and login to it with Windows Authentication:
If the login suceded, then you can make a right click on "Databases" in the tree and select "New Database..." :
In the new window, enter the name of your database (ex: AlertDB) and click on "OK":
If every is good, the database created must be appear in tree:
After creating the database, we need to define a user to access to it.
Creating user for Alert database
To give access to Alert to the database created, we must create a new login.
The login used for connection must be added to the list of logins:
In the following screenshot, we have define a SQL server authentication with login "AlertUsr" and a specific password. We have disabled "Enforce password expiration" to be sure than Alert can connect to SQL server anytime. And finally we have selected the database "AlertDB" as default database :
In order to give the right to Alert to create tables and insert/read datas on it, we must give access to the user AlertUsr to db_datareader, db_datawriter and db_owner on database AlertDB.
Finally, the login must be enabled and have access to the database engine:
Authorize SQL server authentication
Access to SQL Server is done either with Windows authentication or SQL authentication.
Windows Authentification
Connecting via Windows authentication is preferred if Alert and SQL server are on the same machine. For a remote installation, make sure that the access right of the operator who is owner of the Alert task allows a connection to SQL Server.
SQL Authentification
SQL Server options should allow connection via SQL Server Authentication. To access the connection properties, check the SQL server options with the tool "SQL Server Management Studio".
For general properties: Right click the root of SQL Server.
The options of interest are in "security."
Check that the authentication mode accepts the SQL Server Authentication.
Furthermore, if the connection is made from a remote station, it must be allowed. To do this, validate the permission to remote connection in the "Connections" page.
Troubleshooting
For a local connection, connection protocols are configured by default. However, for a remote connection, you must enable IP connections. To do this, you will use the tool “SQL Server Configuration Manager“.
We will enable the protocols of the IP connections.
First, enable the TCP/IP connection of “SQL Server Network” as follows.
Then, one must specify properties for ports. Double click on “TCP/IP“.
Ensure that the configuration is similar to the following captures:
We are only interested in the properties of ”IP All".
Following these changes, it is necessary to restart the SQL Server service:
A reboot can also be useful if the connections are always rejected after these changes.
Now remote connections are set up.