Detailed steps for Navicat to remotely connect to SQL Server and convert to MySQL

Preface

Recently, I encountered a program using SQL Server in a project. I had never used this database before, so I tried to convert it to MySQL format. However, I didn’t want to install SQL Server locally, so I decided to install it on a remote Windows server and connect to it locally. However, I encountered many problems during the implementation process, which I recorded here. It is convenient for your own home or everyone to refer to when needed. Let’s take a look at it below.

Environmental Information

Let me first talk about the environment. The server system is Windows Server 2016 Datacenter and the database is SQL Server 2016 Express.
The local system is Windows 7 and the software used is Navicat Premium 11.2.7.

The installation of SQL Server went smoothly, so I won't go into details here. Don't forget to install Microsoft SQL Server Management Studio as well...
I have never used SQL Server before, and I don't even know how to find my username and password after installing it...

Get Username and Password

1. Open Microsoft SQL Server Management Studio and select Windows Authentication when logging in. You can log in without a password.

2. Right-click the open connection, select Properties, select Security, and then set it to SQL Server and Windows Authentication mode.

3. Select Connection, then select Allow remote computers to connect to this server (seems to be the default), click OK to save.

4. In the opened connection, expand Security, expand Logins, open the properties of the sa user, set a new password and save it. I guess this sa user is similar to the root user in MySQL. We will use this account and password to log in later.

5. Open SQL Server Configuration Manager, expand SQL Server Network Configuration, open the SQLEXPRESS protocol (mine is the default for the Express version, find the corresponding one for others), open its TCP/IP properties, select Yes for Enabled and Listen All in the Protocol tab, then in the IP Address tab, modify the configuration of IP1 at the bottom, set TCP Port to 1433, confirm and save, as shown in the figure below.

6. Restart the SQL Server service. You can restart it in SQL Server Configuration Manager. Some people say that you must start the SQL Server Browser service, but it is not necessary in actual tests.

7. Add exceptions to the firewall. Control Panel -> System and Security -> Windows Firewall -> Allow an app or feature through Windows Firewall -> Allow other apps -> Path -> [SQL Server installation directory]\MSSQL\Binn\sqlservr.exe, confirm and save all the way, and you're done!

Local Navicat connects to remote SQL Server

Open Navicat, create a new connection -> SQL Server -> connection name is arbitrary, fill in the remote server address for IP address, select SQL Server authentication, the username is sa, and the password is the password just now. The connection test will succeed. If it fails, it is probably because there is a problem with the above configuration, then confirm and save.

It seems that everything said at this step is nonsense. -.-

Convert SQL Server data to MySQL data

The premise is that the MySQL database has been set up locally.

1. Create a new MySQL connection and create a database after connecting. (No need to say more)

2. Double-click the newly created MySQL database, select Import Wizard, select ODBC, and click Next.

3. In the Import from option, select Microsoft OLE Provider for SQL Server, and click Next.

4. Then set the server information in the connection tab, test the connection successfully, select the database to be converted, and then confirm.

5. Select the table to be imported, select the import mode, start importing, wait a while, and you're done!

Summarize

The above is the full content of this article. I hope that the content of this article can be of some help to your study or work. If you have any questions, you can leave a message to communicate.