- win10 64bit, SQL Server Express 2012, SQL Management Studio 17.4 (SSMS)
- win10 64bit, SQL Server Express 2017, SQL Management Studio 17.4
All links open in new tab. Use them in case you don’t have the necessary software installed.
Check if SQL Server allows SQL Authentication
Open SQL Server Management Studio (you can just type SSMS on Windows start). If it’s not installed you can get it free from Microsoft (or search SSMS download on Google, make sure you get it from a Microsoft website).
Connect to the desired server. After the connection is established, right-click on the server’s name and select Properties. Select Security and make sure the SQL Server and Windows Authentication mode is checked.
You will be informed that some changes need server restart in order to take effect. To do so, right-click again on server’s name and select Restart. Answer Yes to next two messages.
Create a SQL user
To create a new user, expand Security from the server tree, right-click on Logins and select Add new login.
On the General tabs, fill in the user and password, check SQL Server authentication and un-check Enforce password expiration. On the bottom of the page, select the database you want this new user to connect by default. If it’s now created yet, you can leave it as it is, master.
Now, on the Server Roles tab, check sysadmin.
Test your new user:
- disconnect the server
- connect again, using this time SQL Server authentication, the new user and its password.
TCP/IP and Port settings
On Windows Start type Computer Management (or you can find it on Control Panel\All Control Panel Items\Administrative Tools). All settings will be done here.
Under Services and Applications, find Protocols for SQLExpress. If TCP/IP is disabled, right-click on it and select Enable. Click OK to the notification that a server restart is needed.
Now set the port: double-click on TCP/IP, select IP Settings tab and scroll down to IPAll. Here, delete any value from TCP Dynamic Ports and type 1433 to TCP Ports. Click OK and OK.
Final step, restart the SQL server: On the left side of the Computer Management go a bit up and click on SQL Server Services. On the right side, right-click on SQL Server (SQLEXPRESS) and select restart. Wait until stop and start dialogs disappear.
On the start button type “firewall” and then click on the Windows Defender Firewall. Right-click on Inbound and select New Rule.
Rule type: choose Custom.
Protocol and Ports: select Protocol type TCP and Remote port 1433 – the port you chose for SQL Server communication.
Scope: Here you specify the IP of the PLC as the remote IP. Click Add and type it. If more than one PLC, use Add button to add them all.
Click Ok and go to Name and define a meaningful name for this rule, like PLC-to-SQL connection.
That’s all, now you should be able to connect your PLC to the SQL database.