Connecting to Microsoft SQL Server data source

Before data can be pulled from Microsoft SQL Server, Cayosoft Administrator must be granted access to Data Source using an SQL Account. The account must have the correct permission to access any database, table or view from which data will be read by the rule.

Microsoft SQL Server requirements

• Microsoft SQL Express, latest 3 supported versions

• Microsoft SQL Server, latest 3 supported versions

• Microsoft SQL Management console

• Microsoft SQL Server Browser Service must running on the Microsoft SQL Server

To determine your version of Microsoft SQL Server, follow the instructions in the Latest updates and version history for SQL Server article.

SQL Authentication Requirement

The account used by Cayosoft Administrator must be set to use SQL Authentication. So the account you create must be in the SQL Authentication or Mixed mode authentication.

SQL Instance, Table and View Naming Requirements

You can specify the SQL instance name on the specific computer in this format: SERVERNAME\INSTANCENAME. When entering the SQL Instance into Cayosoft Administrator, the name must be entered in all upper case characters.

You can run multiple instances of the MS SQL Database Engine on a server. One instance can be the default instance. The default instance has no name. If you specify only the name of the server in the Instance field in automation rule or in Utils extension settings, the connection is made to the default instance.

You can specify Default SQL settings in the Utils extension settings and use these settings as Default in the automation rules. Also, you can use custom SQL settings (for instance, credentials, database) in every automation rule:

1. Click the ... button near the fields SQL database name and SQL credentials.

2. For the SQL instance, you need to specify the instance name.

Permissions to read table and view column headings for use by the Cayosoft Object Picker

Cayosoft Administrator allows users to select fields from the data source using what is known as an Object Picker dialog box.

For the object picker dialog box to function properly, the connection account must be able to connect to the SQL MASTER database and have the following access:

• Read on sys.Databases

• Read on sys.columns

• Read on sys.schemas

Permissions to read database tables and views

Cayosoft Administrator must be able to connect to SQL Server and read the tables and views from the desired database.

Creating a Cayosoft SQL Server Account

There are two simple ways to create a SQL Account that can be used by Cayosoft Administrator to access Microsoft SQL Databases.

• First, an account can be created manually using the Microsoft SQL Management Studio.

• Second, those familiar with running SQL Scripts can use the script provided below to create the account.

In both cases, the permissions necessary for the Object Picker and for Cayosoft Administrator to read database tables and views are granted to the connection account.

NOTE: Select manual -or- Script creation, you do not need to execute both.

Manually create a SQL account and assign permissions

In this example, we create a SQL Account named CayoSQLUser, but the name can be any valid name of choice. The CayoSQLUser account is granted permissions to read the tables and views within the HR_DATABASE database and read the column headings for a simplified selection of fields. Please note, be sure to replace HR_DATABASE in the script with the actual name of the database that contains the data to be accessed.

1. Open Microsoft SQL Management Studio, connect to SQL server as an administrator.

2. In the Object Explorer, expand Security > Login, then select pop-up menu item New login.

3. Set a new login name as CayoSQLUser, select the SQL server Authentication setting, specify login password and uncheck the User must change password at next login checkbox. Leave other options set to default.

4. Select the User Mapping page at the left pane to assign the roles:

5. Select checkbox near the HR_DATABASE, than select roles db_denydarawriter and db_dataowner.

   (Replace HR_DATABASE in the previous line with the name of the database that contains the table or view from which data will be read).

6. Select the checkbox near the master, then select roles db_denydatawriter and db_dataowner.

7. Click OK to save the changes.

Script to create a SQL account and assign permissions

For people familiar with SQL Script, you can use the script below to create an access account and assign the correct permissions.

The script creates a SQL Account named CayoSQLUser, but then it can be any valid name of choice. The CayoSQLUser account is granted permissions to read the tables and views within the HR_DATABASE database and read the column headings for a simplified selection of fields. Please note, be sure to replace HR_DATABASE in the script with the actual name of the database that contains the data to be accessed.

Script begins below this line ===========USE [master]; /*Create new login for Ca CREATE LOGIN [CayoSQLUser] WITH PASSWORD=N’P@$$word123zxy’, DEFAULT_DATABASE=[ma CREATE USER [CayoSQLUser] FOR LOGIN [CayoSQLUser];
GRANT CONNECT TO [CayoSQLUser];
GRANT SELECT ON sys.Databases TO [CayoSQLUser]; GRANT SELECT ON sys.columns TO [CayoSQLUser];
GRANT SELECT ON sys.schemas TO [CayoSQLUser]; /*Grant CONNECT and SELECT access
 
USE [HR_DATABASE];
CREATE USER [CayoSQLUser] FOR LOGIN [CayoSQLUser]; GRANT CONNECT TO [CayoSQLUser];
GRANT SELECT TO [CayoSQLUser]; Script ends above this line ===========

Test Cayosoft Administrator SQL Server connection

After a SQL account has been created for Cayosoft Administrator the SQL Connection can be configured and tested. If issues arise, the most likely cause is firewall issues between the two servers.

Configure and test the connection

1. In the Cayosoft Administrator Console, navigate to the Utils extension settings.

2. To the right of the Credentials field, click the Select button. (The select button is the small gray ... button).

3. Enter the CayoSQLUser account name and password.

   (If you choose a name different from CayosSQLUser, enter the name you have chosen).

4. Click OK.

5. In the Instance field, enter the name and SQL Instance to which Cayosoft Administrator is goint to be connected.

   NOTE: The name must be in the SERVER\SQLINSTANCENAME format and be entered in upper case letters.

7. In the Database field, select or enter the name of the database.

Troubleshoot the Cayosoft to SQL Connection

Network Connection Requirements

Firewall settings on both the Cayosoft Administrator Server and the Microsoft SQL Server must be configured to allow the servers to communicate. For more information on performing this task, see the following Microsoft article: Configure a Windows Firewall for Database Engine Access.

Network/Security Considerations

• The firewall must allow access to SQL Server on the Cayosoft Administrator and SQL Servers.

• IE Enhanced Security Must also be set to allow access.

Required SQL Services

Microsoft SQL Server (SQL Express is OK) Microsoft SQL Server Browser must be running.

Account used for connection

Authentication must be in SQL Authentication or Mixed Mode Authentication. SQL Account should be sysadmin and public role holder

Specifying server name

• Use the default instance, provide only server name.

• Use the SERVER/INSTANCE naming format (Server/Instance name must be in all capital letters). Example: SERVER/SQLEXPRESS

Specify table or view names in rules

Table name should not contain special characters like if possible. Do not prefix table names with dbo.

Related articles

How to use Azure SQL services in Cayosoft Administrator SQL provisioning rule