Process and result of connecting to SQL Server on windows from Linux virtual environment using JDBC driver

This time, we will build a Linux virtual environment on windows, install the JDBC driver, and then display the result of connecting to SQL Server on windows using the JDBC client!

table of contents

1. Download VMware (windows environment)
2. Download & install SQL Server & create a simple database and table (windows environment)
3. SQL Server remote connection settings (windows environment)
4. Download and install CentOS (virtual environment)
5. JDK installation (virtual environment)
6. Obtain PROGRESS JDBC driver (virtual environment)
7. DBeaver EE download & driver settings (virtual environment)
8. DBeaver EE JDBC URL connection settings (virtual environment)
9. Execution of SQL statement (virtual environment)

Execution environment

Windows10 home 64bit OS build: 18362.778 CentOS8 (virtual environment) JDK11 (virtual environment)

※important point

The apparent hardware is the same in the local environment on windows and the Linux virtual environment, but since it is a completely different execution environment, those who have already installed software such as JDK in the local environment will download and install it again in the Linux environment. is needed.

procedure

1. Download VMware (windows environment)

First, go to https://my.vmware.com/en/web/vmware/free#desktop_end_user_computing/vmware_workstation_player/14_0 and download VMware Workstation 14.1.8 Player for Windows 64-bit Operating Systems. (Figure 1). ), Please execute the extension exe to complete the installation.

Figure 1

2. Download & install SQL Server & create a simple database and table (windows environment)

Basically, go to https://www.tairax.com/entry/Microsoft-SQL-Server/Install and follow the instructions there. After completing this procedure, the SQL Server download and installation will be completed. Then, access https://www.tairax.com/entry/Microsoft-SQL-Server/How-to-make-database for how to create a database, and https://www.tairax.com for how to create a table. Please refer to / entry / Microsoft-SQL-Server / How-to-make-table.

3. SQL Server SQL Authentication Settings & Remote Connection Settings (windows environment)

At this stage, you can only log in to SQL Server with windows authentication. However, you need to set up SQL authentication to connect remotely. First, log in to SQL Server with windows authentication (Fig. 1.1).

Figure 1.1

Then, for the subsequent settings on SQL Server, refer to https://creativeweb.jp/fc/remote/ and https://creativeweb.jp/tips/firewall-sql-server/. By the way, in the former item of enabling TCP / IP of the reference destination, you can select fixed port or dynamic port, but please select the fixed port. Then set the remote IP address in the firewall settings of the latter reference, which you can click on the gear-like mark to the right of the virtual environment settings (shown in Figure 3) / network wired. Enter both the IPv4 address inside and the default address (Fig. 1.2). This completes the remote setting on the SQL server side.

Figure 1.2

4. Download and install CentOS (virtual environment)

Basically, please follow the procedure of https://qiita.com/anWest/items/c4bfd41f1dfbe90a0d5a and point out some points to note. First, for the URL to obtain the CentOS ISO, select the URL that contains "Packages" (Fig. 2). This is because ISO files take a long time to download, but other ISO files are likely to be corrupted.

Figure 2

Next, set some root password and user password while creating CentOS (root is the authority required to install new software such as Java with administrator privileges, and when actually working Log in with user privileges (Every time you start CentOS, you will be logged in as a user, but since you can easily set automatic login, let's google with "CentOS automatic login" etc.)). Make a note of the root and user passwords, as you will often need them.

When all the settings are completed, you can operate with GUI like windows (Fig. 3) (If you enter sleep mode, press the enter key twice. The login screen will appear again).

Figure 3

5. JDK installation (virtual environment)

A Java runtime environment is required to use the JDBC driver that will be downloaded later. Install the JDK there. First open the command line (click on the activity in the upper left and then the second black square from the bottom of the sidebar) (Figure 4).

Figure 4

When the command line comes up, type "sudo yum install java-1.8.0-openjdk java-1.8.0-openjdk-devel" (sudo is the code to run with administrator privileges). Enter the user password there. Then the installation will start, but if it stops in the middle, press the enter key or y to proceed. When the installation is completed, it will be in the standby state for command input as shown in Fig. 5.

Figure 5

Finally, type "java -version" to make sure it's installed properly. If it is installed properly, the java version information will be displayed as shown at the top of the screen in Fig. 5.

6. Obtain PROGRESS JDBC driver (virtual environment)

Use a browser on CentOS to access https://www.ashisuto.co.jp/datadirect/app_download/ and download DataDirect Connect for ODBC UNIX / Linux (64Bit) (when downloading a Linux file) Please select "Save file", which is common to Linux (Fig. 6).

Figure 6

After downloading the driver, you will find a file called PROGRESS_DATADIRECT_JDBC_SQLSERVER_6.0.0.zip in your home or download directory. Right-click on it and click Expand Here. Then create a new folder and move the contents files. Then execute PROGRESS_DATADIRECT_JDBC_INSTALL.jar in the contents file on the command line (start the command line, move to the hierarchy where the file you want to execute is with the cd command, and enter "java -jar PROGRESS_DATADIRECT_JDBC_INSTALL.jar -i console". Will be installed (Fig. 7)) When the installation is complete, sqlserver.jar should be located in / home / okazakisoichiro / Progress / DataDirect / JDBC_60 / lib.

Figure 7

7. DBeaver EE download & driver settings (virtual environment)

Go to https://dbeaver.com/download/ and download the Linux RPM Package 64bit (Installer) (Figure 8).

FIG. 8

Then, since the RPM package you downloaded earlier is in your home or download directory, start the command line and cd to that level (see Figure 7) on the command line "sudo rpm -ivh dbeaver- Type .rpm (enter the version of the RPM package you downloaded for varsion). This will install DBeaver. Enter "dbeaver" on the command line, and if DBeaver starts up, you can install it (Fig. 9).

Fig. 9 (If a screen like this appears, it is successful (Fig. 9 is the display after JDBC connection, so it is actually a simpler display))

Now, let's set the driver. Select the database in the DBeaber menu bar that you just started and start the driver manager (details are described in 5. Registering the BigQuery connector with Dbeaver at https://qiita.com/zakiokasou/items/2e9297e268770351a300). After starting, fill in the driver name, class name, and library. Click "JDBC Driver" for each driver name, "com.ddtek.jdbc.sqlserver.SQLServerDriver" for the class name, and Add File next to the library form to get it in step 6 of this article. , Sqlserver.jar. Then press OK (Fig. 10).

FIG. 10

8. DBeaver EE connection settings (virtual environment)

Then click Database> New Connection on the Dbeaver menu bar. The JDBC driver you named earlier exists there, so select it and press Next. Then, the screen as shown in Fig. 11 appears.

FIG. 11

Then enter the following URL in the JDBC URL: "jdbc:sqlserver://:;databaseName=;user=;password=" Modify the contents of <> as follows. server → Left side of \ of the server name when logging in to SQL Server (Fig. 12)

port → Fixed port number set in the reference destination (https://creativeweb.jp/fc/remote/) of step 3 of this article (1433 if the procedure is followed)

databaseName → database name set in the reference destination (https://www.tairax.com/entry/Microsoft-SQL-Server/How-to-make-database) of step 2 of this article (sample if the procedure is followed)

user, password → Login name and password when logging in to SQL Server with SQL authentication (see Fig. 12)

FIG. 12

When you have finished entering the JDBC URL, press the test connection at the bottom left of Figure 11. Hopefully you will get a pop-up saying that you are connected. Press OK at the bottom right to complete the connection settings.

9. Execution of SQL statement (virtual environment)

There is an item called JDBC driver on the left side of the initial screen of DBeaver, so right-click on it and select the SQL editor (Fig. 13).

(Fig. 13)

Then you can operate SQL on the right side of the screen, so "SELECT * FROM [sample]. [Dbo]. [Human] "([] should be [sample] and [human] if you follow the procedure, depending on the database and table settings in the reference of step 2 of this article) Enter and enter the execute button (the shape of the triangle tilted 90 degrees) in the center of the screen. I was able to get the data from SQL server at the bottom right of the screen (Fig. 14) (It is necessary to enter the sample value in SQL Server in advance to display it as shown in the figure).

FIG. 14