How to Connect to SqlServer

DbSchema can connect to SqlServer only after TCP/IP is enabled in the Server. By default this is disabled.
Also the DbSchema required Host name is different from the Windows server name. Here in detail: After this you can connect from DbSchema : If you get issues with the connections :

DbSchema supports also connectivity to Microsoft Azure

If you get errors while connecting, please read :

How to enable TCP/IP Connection and Set The Port

SqlServer TCP/IP connections are not enabled by default. You won't be able to connect to the database unless you enable them.

In the SQLServer Configuration Manager go to 'SqlServer Network Configuration'/ 'Protocols' and:

  1. Enable TCP/IP
    Configuration Manager
  2. Right-click on properties.
    Go to the IP Addresses tab, scroll to the bottom and you'll find the option IP All. Enter here 1433.
    ATTENTION! The IP ALL / PORT value is the correct one to set, this one is on the bottom !
    TCP Properties

Use 1433 also in the DbSchema Connection Dialog for the port value. You may need to restart SqlServer or the computer for this to take effect.

In DbSchema Connection dialog press the 'Ping' button to make sure the port is active and reachable. If it does not work remember to disable firewalls.

How to Create Rule on Firewall

The firewall running on the database server is blocking remote TCP/IP connections. The easiest way to enable TCP/IP through port 1433 is to execute this line from Command Prompt. Check first the configured port as in the chapter above. 
netsh advfirewall firewall add rule name=SQLPort dir=in protocol=tcp action=allow localport=1433 remoteip=localsubnet profile=DOMAIN
Alternative you can use this steps:
  1. On the Start menu, click Run, type WF.msc, and then click OK.
  2. In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane (upper right corner).
  3. In the Rule Type dialog box, select Port, and then click Next.
  4. In the Protocol and Ports dialog box, select TCP. Select Specific local ports, and then type the port number of the instance of the Database Engine, In my case we are using the default which is 1433. Click Next.
  5. In the Action dialog box, select Allow the connection, and then click Next.
  6. In the Profile dialog box, I am going to Leave Domain turned on and turn private and public off. Then click Next.
  7. In the Name dialog box, type "Allow SQL 1433 Inbound" and for a description I am putting in the same. Then click Finish.

Enable Database Authentication (Mixed Mode, required for Mac DbSchema users)

By default only the Windows Authentication is enabled. This will allow login on the database only using the windows user and not using the 'sa' user. If DbSchema is running on a Mac system, you won't be able to connect using Windows authentication, therefore please use this method to connect.

To be able to connect to the database using the 'sa' user or other database user, the mixed authentication mode should be enabled.

Take this steps to enable mixed mode authentication:

First, login to your server. Click on Start > Programs > Microsoft SQL Server Select "SQL Server Management Studio Express" / "SQL Server Management Studio", depending on your version of SQL Server.

Right click the server name and select "Properties". Click "Security".
SQL Properties

A dialog will open. Under "Server authentication" select "SQL Server and Windows Authentication Mode".

Right click the server name and select "Restart". Wait a few moments for the service to restart before proceeding.
SQL Config Properties
To review the current configured authentication method, from the previous dialog 'Server Properties' choose the 'View Connection Properties' in the bottom. In the Properties Dialog the configured authentication will show up.

Change 'sa' password on Server

On database server, in Command Prompt execute: 
C:\>OSQL -L
 MYDESKTOP

C:\>OSQL -S MYDESKTOP -E
1> sp_password NULL, newpassword, 'sa'
2> GO
1> exit

Restart the server.
For changing password for other users replace 'sa' with the actual user name.

Connect to your SqlServer

Connecting to SqlServer from DbSchema first decide for the method to connect ( Windows authentication or database authentication- mixed mode). Then please check carefully the host, this is not the Windows Server name, but the TCP/IP name of the computer! Read below for more details on how to find the server host name.
Standard Connection
From the driver combo you can choose different connection types:
Connection Methods
  • The Host is the name or the IP of the database server.
    To find the hostname execute 'hostname' from the server command prompt. Open the command prompt by typing 'cmd' in the start menu.
    hostname
    To find out the IP, on database server open the command prompt ( type cmd in Start Menu ) and execute ipconfig. Look for IP-address:
    ipconfig
    To find the computer name open the File Explorer, right click 'Computer' and select 'Properties'. Alternative press Windows Key plus 'Break'.
    Host Name
  • Default Port is 1433. Check the chapter How to enable TCP/IP connections for the configured port value.
  • Standard user is sa
  • The Instance it's optional, use it only if you are connecting to a certain instance of your SqlServer
  • The Database it's optional as well, if you don't select a certain database, it will connect to the default database.
Tip Windows Server Name and Host name are different. Host name is resolved via DNS and can be tested with 'ping' from command prompt. Server name is used only in Windows networks.

Connect to Azure Cloud

DbSchema connection combo shows a dedicated database 'Azure'. Connections to Azure are supported using both drivers, native and JTDS. Just enter in the host field the server as in Azure, sample: 'sampleserver.database.windows.net', port is 1433. The username and password as set in Azure. Also make sure you set in Azure Firewall setting a rule for the computer IP. You may see the real IP when connecting first, in an exception message.

Connect using Active Directory Security

Connections using Active Directory security are allowed in two modes:
  1. Using Username and Password In the connection dialog choose the 'Active Directory Authentication with User and Password' connection method.
  2. Using Active Directory Integrated Security This method is using a DDL file already installed in the Java Home/bin folder by DbSchema. You can check the Java Home from the Help / About dialog. After this choose the 'Active Directory Integrated' connection method in the Connection combo.

Setup Encryption or Use Custom JDBC URL

DbSchema has few pre-configured few JDBC URLs. In some cases this may be not enough. You can edit a custom URL in the Connection dialog / 'JDBC URL' tab. To find the right URL you have to use read the driver documentation: Please write to us back about the connection URL which works for you, we will improve our predefined patterns.

Enforce SSL TLS 1.2 via JTDS Driver

If you try to connect using JDTS driver, and the TLS 1.2 is required, please have a look on this article

Debug Connectivity Issues

  1. Check Network:
    From the machine where DbSchema is running execute: 
    ping HOST_NAME
    If this is failing it means the host name is wrong. In this case look on how to find the server host name as described in Connect to SqlServer.
  2. Check if the port is open.
    To verify that SQL Server listening port, you can use the telnet from the computer where you run DbSchema. If not installed, you can install Telnet like this:
    1. Click Start > Control Panel.
    2. Click Programs and Features.
    3. Click Turn Windows features on or off.
    4. In the Windows Features dialog box, check the Telnet Client check box.
    5. Click OK. The system installs the appropriate files. This will take a few seconds to a minute.
    Assume that SQL Server's IP address is 192.168.1.1 or host name 'samplehost', port 11433, you can run the following command: 
    telnet 192.168.1.1 1433
or
telnet samplehost 1433
    If the TELNET successful, then the result will be a only a black screen with cursor in flash. If unsuccessful, then you will get an error message. In this case please the port may be wrong. Please How to enable TCP/IP connections regarding on how to see the configured TCP/IP port.
  3. Check On Server the Port value and TCP/IP to be enabled
    Read the chapter How to enable TCP/IP connections. There you will find also instructions about finding the configured TCP/IP port.
  4. Create an exception for SQL Server in Windows Firewall:
    Follow the steps from the Firewall chapter.
  5. Enable the SQL Server Browser service:
    If you connect to a named instance, make sure the SQL Server Browser service by using SQL Server Configuration Manager is enable or specify the TCP port when connecting to it.

Common Error Messages

    Java.net.ConnectException or Network error IOException: Connection refused: connect
  1. Check if the Hostname and Port are correct and try again
  2. Enable TCP/IP connections
    Database or Network Error
  • Check if the username, password and database name are correct and try again.
    java.net.UnknownHostException:
  • Check if the hostname / ip is correct and try again.

Permissions Required to Reverse Engineer the Schema from the Database

DbSchema has two ways to reverse engineer the schema: one using DDL, and one using the methods from the JDBC Driver.

The method using DDL is creating a store procedure sp_gettableddl() in the database, used to generate the table DDL statements from the system tables. This is more elaborate and can read the history tables, temporal tables, etc. This requires the permissions to create a procedure in the database. If this fails, the second method, using the method call from the JDBC Driver is used.

The methods from the JDBC Driver are calling the connection.getDatabaseMetaData().getTables(), getColumns(), etc. This is basically calling sp_tables(), sp_columns(), sp_statistics()... The required permissions are VIEW SERVER STATE. Please write us back if you test this.