Solving SQL Server Connection Problem

Upon database modeling with Visual Paradigm, you always need to connect to the database server to perform generation of your database and reverse engineering of your ER model. However, you may encounter problem in connecting to the Microsoft SQL Server, which is a great obstruction to your modeling. In this article, we will provide ways to help you to check whether your configurations for connecting to SQL Server are correct.

Inspect Server Settings

There are three significant aspects that users may neglect, which lead to the failure of the connection to the SQL Server.

Enable TCP/IP Connection

To check whether your server enabled to TCP/IP connections:

1. Open your SQL Server Configuration Manager.
   

   Open SQL Server Configuration Manager

2. Expand SQL Server Network Configuration and select Protocols for SQLEXPRESS.
   

   Select Network Configuration

3. Take a look at TCP/IP, which requires to be Enabled. If yours is Disabled, right-click on it and select Enable in the popup menu.
   

   Enable TCP/IP Connection for SQL Server

4. Also, you may take a look at the Port setting of your server. Right-click on TCP/IP and select Properties in the popup menu.
   

   Open TCP/IP Properties

5. Click the IP Address tab in the TCP/IP Properties dialog box.
   

   Switch to IP Address tab

6. Under IPAll section, you can see the TCP Port (default: 1433) and you can edit the port of your server.
   

   Configure default port for SQL Server

7. You will need to restart server after changing any setting in SQL Server Configuration Manager. You can right click on the server instance and select Restart from popup menu.
   

   Restart SQL Server

Allow Remote Connection

Your need to make sure that remote connection to your SQL Server is enabled. To verify this setting:

1. Startup your SQL Server Management Studio.
   

   Launch SQL Server Management Studio

2. Right-click on the server and select Properties in the popup menu.
   

   Open Server Properties

3. In the Server Properties dialog box, select Connections.
   

   Select Connections

4. Check the checkbox of Allow remote connections to this server.
   

   Turn on Allow remote connections to this server

Authentication Method

We recommend users to run their SQL Server with mixed authentication mode. To inspect the authentication mode of your SQL Server:

1. Open SQL Server Management Studio. Then, right-click on the server and select Properties in the popup menu.
2. In the Server Properties dialog box, select Security.
   

   Select Security setting

3. Make sure Server Authentication is set to SQL Server and Windows Authentication Method.
   

   Select SQL Server and Windows Authentication mode

Make sure Hostname and Port are correct

Back to your VP application, have a check in your database configuration and see if you have entered the Hostname and Port correctly.

1. Select Tools > Database > Database Configuration… in the VP application.
   

   Open Database Configuration Dialog in Visual Paradigm

2. Select the Language, Server, Version and the Driver in Database Configuration dialog.
   

   Select SQL Server for configure connection settings

3. Enter Hostname, which must be either the IP address of your computer, a mapped host name or the computer name.
   

   Specify the host name or IP address of SQL Server

4. Enter the port of your SQL Server. It’s 1433 by default, but if you have edited the port for your SQL Server or running on other named instance, you need to enter the corresponding port.
   

   Specify the port number of your server instance

Driver File

Visual Paradigm supports to connect to SQL Server with various JDBC driver files. But we recommend users to use the jDTS driver. Visual Paradigm can help you to download and setup the jDTS driver automatically. To download the driver:

1. Select Tools > Database > Database Configuration.
2. After you have chosen the language, server and driver, click the green down arrow button beside the Driver file field.
   

   Download jDTS driver file

3. VP will download the adapter file for you.
   

   Automatic download driver file

Please note that VP will need to run with sufficient permission in-order to download and install the driver file. You may need to right click on VP’s shortcut and select Run as administrator in order acquire sufficient permission.

Run Visual Paradigm with administrator permission

Some driver files cannot be automatically downloaded. In this case, you can press the … button beside the Driver file field to specify driver file from your file system to make connection with your SQL Server.

Specify your own driver file

Adapter File (Specific for language specified as .NET)

While you are using non-compatible adapter file, you will not be able to connect to the server. The simplest way to get the compatible adapter file is let our application download it for you:

1. Select Tools > Database > Database Configuration.
2. After select .NET as language, MS SQL Server as the server, click the green down arrow button beside the Adapter file field.
   

   Download adapte file

3. VP will download the adapter file for you.

Again, VP will need to run with sufficient permission in-order to download and install the driver file. You may need to right click on VP’s shortcut and select Run as administrator in order acquire sufficient permission.

SQL Server with Windows Authentication

If you are using the Windows Authentication Method, you will need another connection URL in order to connect to the SQL Server.

Java (SQL Server 2005 Microsoft Driver)

jdbc:sqlserver://<server_host>;databaseName=<database_name>;integratedSecurity=true;

Java (jDTS)

jdbc:jtds:<server_type>://[<server_host>:<port>][/<database_name>];domain=XXX

where <server_type> = sqlserver

** Domain Server is required. If no domain server is available, please try domain=workgroup

.NET

Server=%HOST%,%PORT%;Database=%DATABASE%;User ID=%USER_ID%;Password=%PASSWD%;Trusted_Connection=Yes;Domain=%WINDOW_DOMAIN%

You can enter the proper connection URL in the Connection String field in the Database Configuration dialog box to establish connection with your SQL Server.

Establish connection with connection string

Diagnose Connection Issue in Command Line

You can diagnose whether your SQL Server is ready for making connection by using Telnet command. For Microsoft Windows, Telnet is not installed by default, you need to install it before running the telnet command.

Install Telnet Client

To install the Telnet client on your Windows:

1. Open Control Panel from the Start.
   

   Open Control Panel

2. Select Programs and Features in the Control Panel.
   

   Select Programs and Features

3. Under Programs and Features, select Turn Windows features on or off.
   

   Turn on Windows features

4. Scroll down to find the option Telnet Client, check this option and press OK.
   

   Turn on Telnet command

Use Telnet Command to Check is SQL Server Ready for making TCP/IP connection

1. Enter telnet %host% %port% in the command prompt, where %host% and %port% are the host and port of your SQL Server.
   

   Telnet to SQL Server

2. Press Enter to see if it can call the SQL Server. If the telnet can communicate with the host and port you have specified, a blank dialog box will be shown. This means your SQL Server is able to be connected.
   

   Telnet successfully connect to SQL Server

3. If it fails to connect to your SQL Server, there will be a message of failure.
   

   Telnet fail to connect to SQL Server

If you still fail to connect to SQL server with all the above steps of checking, please contact Visual Paradigm Support Team for technical support.