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 コメント 2020-05-30 142135.jpg

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 コメント 2020-05-30 164614.jpg

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 コメント 2020-05-30 171607.jpg

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 https___qiita-image-store.s3.ap-northeast-1.amazonaws.com_0_468508_40f554a0-415e-d42a-1ff7-ef4c073c3a5f.png

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 コメント 2020-05-30 145625.jpg

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 コメント 2020-05-30 151141.jpg

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 コメント 2020-05-30 151735.jpg

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 コメント 2020-05-30 155139.jpg

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 コメント 2020-05-30 160558.jpg

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 コメント 2020-05-30 193827.jpg

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)) コメント 2020-05-30 195333.jpg

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 コメント 2020-05-30 200037.jpg

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 image.png

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 コメント 2020-05-30 201213.jpg

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) コメント 2020-05-30 204338.jpg

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 image.png

Recommended Posts

Process and result of connecting to SQL Server on windows from Linux virtual environment using JDBC driver
Comparison of Windows Server and Free Linux to Commercial Linux
Memo of Linux environment construction using VirtualBox + Vagrant on Windows 10
Verification of how to periodically execute a script on a Linux server on Windows
Build Linux on a Windows environment. Steps to install Laradock and migrate
ODBC access to SQL Server from Linux with Python
How to build a Python virtual execution environment using Visual Studio Code and pipenv on a Windows machine (also Jupyter notebook)
After all, the story of returning from Linux to Windows
Overview of Python virtual environment and how to create it
Building a Python development environment on Windows -From installing Anaconda to linking Atom and Jupyter Notebook-
I want to automatically operate Chrome on Windows from a virtual machine (ubuntu) started using Vagrant
I tried to create a server environment that runs on Windows 10
I tried to create an environment of MkDocs on Amazon Linux
Everything from building a Python environment to running it on Windows
Django development using virtualenv Procedures from virtual environment construction to new project / new application creation and initial settings
Reasons and work notes for the tool / environment kitchen to switch the main PC from Linux to Windows + WSL2
Dual_boot windows10 and linux by using clover on a uefi PC that cannot be booted from nvme
Python 3.6 on Windows ... and to Xamarin.
(Windows10) Install Linux environment and gnuplot.
Create a Linux environment on Windows 10
I tried to execute SQL from the local environment using Looker SDK
Unable to import packages installed in virtual environment with Anaconda on Windows 10
Steps to create a Python virtual environment with VS Code on Windows
Migration from Python2 to Python3 (Python2 is rebuilt as a virtual environment and coexists)