Connection Difficulties with SQL Server

Description

You are unable to establish a successful connection from DataVeil to your SQL Server database.

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 the Hostname/IP address or Port is incorrect then DataVeil shall display an error similar to that shown below:

SQL Server connection error

If you are using Windows Authentication 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:

SQL Server error 4060 

 

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:

 SQL Server enable TCPIP

 

You should also 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.)

SQL Server TCPIP port

 

You will need to 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 you will see an error similar to that shown below. 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:

SQL Server map user to database

 

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.

SQL Server permissions

 

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

Connection empty schema

 

Also please 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 you have completed all of the above steps then you should be able to connect successfully to your SQL Server database.