How to Connect to SqlServer

DbSchema connects to SqlServer via TCP/IP which is not enabled by default in the database, so you have to enable it. Also the Host name which you have to use in DbSchema is different from the Windows server name. Read this document to learn about this. 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.

Start SqlServer Configuration Manager from C:\Windows\SysWOW64\SQLServerManagerXX.msc, where XX can be 13, 14, etc., depending on the server version.

In the SQLServer Configuration Manager go to Protocols and enable TCP/IP

Click on the 'Protocols for SqlExpress', right-click on the 'TCP/IP' and choose 'Properties'.


Go to the IP Addresses tab, scroll to the bottom and you'll find the option IP All enter the desired port by default it should be 1433. This port value you should use also in DbSchema connection dialog port field.


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".

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.

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.

From the driver combo you can choose different connection types:

  • 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.

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 / Advanced Tab. To find the right URL you have to use read the driver docuentation:
  • SqlServer JDBC Driver: https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url
  • JTDS Driver: http://jtds.sourceforge.net/faq.html
Please write us back about the connection URL which works for you, we will improve our predefined patterns.

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.
#