Connection Difficulties with SQL Server

Description
 

A successful connection from to SQL Server cannot be established.

 

Explanation
 

Possible reasons for unsuccessful connections are:
 - The credentials or parameters in the Connection form are not correct
 - The TCP/IP protocol is not enabled in SQL Server
 - The User does not have a Login account on the SQL Server
 - The User's SQL Server Login has not been mapped in SQL Server to access the database
 - The User's SQL Server Login does not have relevant permissions to access the database.
 

 

Solution
 

Step 1 - Ensure that the Connection parameters are correct.

 

If a TCP/IP connection cannot be established, such as because the Hostname/IP address or Port is incorrect, then DataVeil shall display an error similar to that shown below:
 

 

If Windows Authentication is being used and the SQL Server is on the same computer as DataVeil then try leaving all of the fields blank and attempt a connection.

 

If there is a problem with the User or the database name is not recognized then an error similar to that shown below shall be displayed:
 

 

 

Step 2 - Ensure that the TCP/IP protocol is enabled and port configured in SQL Server
 

The TCP/IP protocol in SQL Server must be enabled in order for DataVeil to connect successfully. If it is not enabled then you will see a dialog similar to the first screen capture shown in Step 1 above.

To verify or enable the TCP/IP protocol, open SQL Server Configuration Manager.

In the left panel, select 'SQL Server Network Configuration' -> 'Protocols for <your-instance>'. The protocols and their statuses shall be displayed in the right panel. Ensure that the TCP/IP status is 'Enabled'. If it is not, then you can enable it by right-clicking on it and selecting 'Enable' as shown below:
 

 

 

Verify that a TCP port is configured to accept connections. To do this, right-click on TCP/IP (as above) and choose 'Properties'. Then select the 'IP Addresses' tab. Review the settings and ensure that 'TCP Port' field is assigned a number for connections. If these are all blank then you can just scroll all the way to the bottom to the section 'IP All' and assign a port number there. A suggested value is 1433 (which is typically used for SQL Server.)
 

 

Restart the SQL Server service in order for the changes to take effect. This can also be done from SQL Server Configuration Manager as shown below:
 

 

 

Step 3 - Ensure the User has been mapped to the Database in SQL Server
 

The User must be mapped to use the Database and have Read, Write and DDL permissions.

If a User attempts to connect to a database where the User has not been mapped to that database then an error similar to that shown below shall occur. This error may also be shown if the database name is unknown to SQL Server.
 

 

3a) Verify that a Login for User exists or create one
 

To verify or configure an SQL Server Login for the User, use SQL Server Management Studio.

If the User does not appear under Security -> Logins then you can create the Login by right-clicking on Logins and selecting 'New Login...'
 

 

In this example, the domain User at the workstation running DataVeil is 'DOM2012\Ross' and is using Windows Authentication to connect to SQL Server. Therefore, the SQL Server login created for this user is shown below:
 

 

3b) Verify Mapping to Database is enabled with Read, Write and DDL permissions
 

The User Login must be mapped to the Database and have Read, Write and DDL permissions.

If you just created the Login, then you can click on the 'User Mapping' option in the left pane in the 'Login - New' dialog shown above.

If the Login already existed then you can just right-click on the user Login and choose 'Properties' which will show a similar dialog to that above; then click on 'User Mapping'.
 

 

The SQL Server User Mapping page is shown below. You can see that the database 'kona' has been mapped to User 'DOM2012\Ross' (checkbox has been selected) and that the necessary Read, Write and DDL permissions have been enabled: db_datareader, db_datawriter and db_ddladmin. You could have also just selected db_owner instead.
 

 

Note: If an attempt to connect from DataVeil where the User does not have Read permission then an error similar to that shown below shall occur (i.e. SQL Server could not Read the user tables, so 'no user tables were found').
 

 

Also note that in order for DataVeil to be able to mask data in the database, the User Login shall require Write permission to write masked values to the database. The User Login shall also need DDL permission to enable DataVeil to create temporary mapping tables.  

If all of the above steps have been completed then a successful connection to the SQL Server database should be possible.