DbSchema for SQL Server Database

DbSchema is a comprehensive tool for designing and managing SQL Server databases. It provides powerful features, like visual schema design, team collaboration with Git, easy schema deployment, and interactive HTML5 documentation.

How to Connect to a SQL Server Database

For a step-by-step video tutorial, click here

For written documentation, please continue reading below.

Open DbSchema, then click "Connect to Database" on the Main Screen and choose your database system. DbSchema will automatically download the required JDBC driver.

Connect to Database in DbSchema

SQL Server offers two authentication methods: Windows Authentication and SQL Server Authentication for database access.

Note: If you are a macOS user, Windows Authentication is not supported directly. Instead, you will need to use SQL Server Authentication

Preparing for Connection in DbSchema

Before starting the connection process in DbSchema, ensure the following conditions are met:

  1. The The SQL Server is running: Verify that the database server is operational and ready to accept incoming connections.
  2. TCP/IP connections are enabled: Configure the network settings of the database to allow connections via the TCP/IP protocol.
  3. Firewall Rules : Ensure that the firewall on the server permits traffic through port 1433, which is commonly used for database connections.

I. Configure the Local Connection in DbSchema

In DbSchema, there are 3 methods for connecting to your local database.

Method 1: This is the easiest connection method: from JDBC URL choose "Windows Authentication, Current User".

  • With this option no other details are needed because it authenticates using the credentials of the logged-in Windows user.

Connection Dialog

Method 2: From JDBC URL choose "Windows Authentication".

Connection Dialog

Method 3: From JDBC URL choose "SQL Server Authentication"

Connection Dialog

II. Configure the Remote Connection in DbSchema

In DbSchema, there are 2 methods for connecting to your Remote database.

Method 1: From JDBC URL choose "SQL Server Authentication".

  • Select the Remote computer or custom port
  • Here, you can enter the IP address or the Hostname (Windows Computer Name) of your remote SQL Server to connect from your local computer to another remote machine.
  • Enter the database username and password that were configured during the SQL Server installation process.

Connection Dialog

Method 2: From JDBC URL choose "Windows Authentication".

  • For Windows Authentication to work properly, both machines usually need to be part of the same network or domain, as this ensures that the authentication system can recognize and validate the user credentials across the devices.

Where to Find Essential Information and Configurations

1. In The SQL Server Configuration Management

Start the SQL Server

On the SQL Server Services, right click on the MSSQLSERVER and select Start.

MSSQLSERVER

Enable TCP/IP:

In this application go to "SQL Server Network Configuration" → "Protocols" and:
Configuration Manager

Set the port: 1433

Right-click on TCP/IP → 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

2. In the SQL Server Management Studio

Enable SQL Server Authentication

By default, SQL Server is set to use Windows Authentication only. If you want to connect using the system account or other SQL Server users, you must enable both: SQL Server and Windows Authentication Mode.

Follow these steps to set it up:
  1. Open SQL Server Management Studio (SSMS).
  2. In the Object Explorer, right-click the server name and select "Properties".

    3. SQL Server Management Studio

  3. In the Server Properties window, click on "Security".
  4. Under Server Authentication, select "SQL Server and Windows Authentication Mode", then click OK.

    5. SQL Server Management Studio

    Important: These changes usually require a restart of the SQL Server to take effect:
  5. In Object Explorer, right-click the server name again and select "Restart".
    6. After the restart, the SQL Server will be configured to accept both SQL Server and Windows authentication.

    SQL Server Management Studio

Finding and Managing SQL Server Login Credentials

To find your SQL Server login credentials (database username):

  • Expand Security → Logins to view the list of user accounts.
  • If you have login issues, right-click your user account, select Properties, and ensure the account is Enabled under the Status tab.

SQL Server Management Studio

Finding Your IP Address

Follow these steps to find the domain name of your computer or network:

  • Open Command Prompt: Press Win + R, type cmd, and press Enter.
  • Run the ipconfig Command: In the Command Prompt, type the following command and press Enter:
    • IP adress

These details will help you identify the domain name associated with your network or machine.

How to Find the TCP/IP HostName

You can find the TCP/IP hostname of a Windows server using several methods. Here are a couple of straightforward ways:

Using Command Prompt

  • Open Command Prompt: Press Win + R, type cmd, and press Enter.
  • Type the following command: hostname
Hostname

Using ipconfig

  • Open Command Prompt: Press Win + R, type cmd, and press Enter.
  • Type the following command: ipconfig /all
  • Look for the “Hostname” in the output. This will show the hostname along with other network configuration details.

Using nslookup

  • Open Command Prompt: Press Win + R, type cmd, and press Enter.
  • Type the following command: nslookup <IP address>
  • Replace with the actual IP address of the server. This will return the hostname if a reverse DNS entry exists1.

Using nbtstat

  • Open Command Prompt: Press Win + R, type cmd, and press Enter.
  • Type the following command: nbtstat -A <IP address>
  • Replace <IP address> with the actual IP address of the server. This will display the NetBIOS name table, including the hostname.

These methods should help you find the TCP/IP hostname of your Windows server. If you need further assistance, feel free to ask!

How to find the Windows login username

Using Command Prompt

  • Open Command Prompt: Press Win + R, type cmd, and press Enter.
  • Type the following command: echo %USERNAME%
Windows Login Username

Through the Settings:

  • Open Settings (Windows + I)
  • Go to Accounts.
  • Your username will appear under Your info.

    • Using Task Manager:

  • Right-click the taskbar and select Task Manager.
  • Go to the Users tab, where you'll see the active user and their username.

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

    More useful links:

    If you get issues with the connections : If you get errors while connecting, please read :

    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.