Connecting An S7-1500 To An SQL Database

Connecting an
S7-1500 to an
SQL Database
Siemens
TIA Portal V16 / S7-1500 / Microsoft SQL / TDS Industry
Online
https://support.industry.siemens.com/cs/ww/en/view/109779336 Support
Legal information
Legal information
Use of application examples
Application examples illustrate the solution of automation tasks through an interaction of several
components in the form of text, graphics and/or software modules. The application examples are
a free service by Siemens AG and/or a subsidiary of Siemens AG (“Siemens”). They are non-
binding and make no claim to completeness or functionality regarding configuration and
equipment. The application examples merely offer help with typical tasks; they do not constitute
customer-specific solutions. You yourself are responsible for the proper and safe operation of the
products in accordance with applicable regulations and must also check the function of the
respective application example and customize it for your system.
Siemens grants you the non-exclusive, non-sublicensable and non-transferable right to have the
application examples used by technically trained personnel. Any change to the application
examples is your responsibility. Sharing the application examples with third parties or copying the
application examples or excerpts thereof is permitted only in combination with your own products.
The application examples are not required to undergo the customary tests and quality inspections
of a chargeable product; they may have functional and performance defects as well as errors. It is
your responsibility to use them in such a manner that any malfunctions that may occur do not
result in property damage or injury to persons.
Disclaimer of liability
Siemens shall not assume any liability, for any legal reason whatsoever, including, without
limitation, liability for the usability, availability, completeness and freedom from defects of the
application examples as well as for related information, configuration and performance data and
any damage caused thereby. This shall not apply in cases of mandatory liability, for example
under the German Product Liability Act, or in cases of intent, gross negligence, or culpable loss of
life, bodily injury or damage to health, non-compliance with a guarantee, fraudulent
non-disclosure of a defect, or culpable breach of material contractual obligations. Claims for
damages arising from a breach of material contractual obligations shall however be limited to the
© Siemens 2020 All rights reserved
foreseeable damage typical of the type of agreement, unless liability arises from intent or gross
negligence or is based on loss of life, bodily injury or damage to health. The foregoing provisions
do not imply any change in the burden of proof to your detriment. You shall indemnify Siemens
against existing or future claims of third parties in this connection except where Siemens is
mandatorily liable.
By using the application examples you acknowledge that Siemens cannot be held liable for any
damage beyond the liability provisions described.
Other information
Siemens reserves the right to make changes to the application examples at any time without
notice. In case of discrepancies between the suggestions in the application examples and other
Siemens publications such as catalogs, the content of the other documentation shall have
precedence.
The Siemens terms of use (https://support.industry.siemens.com) shall also apply.
Security information
Siemens provides products and solutions with industrial security functions that support the secure
operation of plants, systems, machines and networks.
In order to protect plants, systems, machines and networks against cyber threats, it is necessary
to implement – and continuously maintain – a holistic, state-of-the-art industrial security concept.
Siemens’ products and solutions constitute one element of such a concept.
Customers are responsible for preventing unauthorized access to their plants, systems, machines
and networks. Such systems, machines and components should only be connected to an
enterprise network or the Internet if and to the extent such a connection is necessary and only
when appropriate security measures (e.g. firewalls and/or network segmentation) are in place.
For additional information on industrial security measures that may be implemented, please visit
https://www.siemens.com/industrialsecurity.
Siemens’ products and solutions undergo continuous development to make them more secure.
Siemens strongly recommends that product updates are applied as soon as they are available
and that the latest product versions are used. Use of product versions that are no longer
supported, and failure to apply the latest updates may increase customer’s exposure to cyber
threats.
To stay informed about product updates, subscribe to the Siemens Industrial Security RSS Feed
at: https://www.siemens.com/industrialsecurity.
Microsoft SQL with S7-1500

Article ID: 109779336, V1.0, 05/2020 2
Table of contents
Table of contents
Legal information ......................................................................................................... 2
1 Introduction ........................................................................................................ 4
1.1 Overview............................................................................................... 4
1.2 Principle of operation............................................................................ 5
1.3 Components Used ................................................................................ 6
2 Engineering ........................................................................................................ 7
2.1 Interface description ............................................................................. 7
2.2 Integration into the user project ............................................................ 9
2.3 Operation of LSql_Microsoft ............................................................... 12
2.4 Error handling ..................................................................................... 15
3 Useful information ........................................................................................... 16
3.1 Microsoft SQL Server Express basics ................................................ 16
3.2 Settings in Microsoft SQL Server Express ......................................... 17
4 Appendix .......................................................................................................... 21
4.1 Service and support ........................................................................... 21
4.2 Links and literature ............................................................................. 22
4.3 Change documentation ...................................................................... 22
© Siemens AG 2020 All rights reserved

Article ID: 109779336, V1.0, 05/2020 3
1 Introduction
1 Introduction
1.1 Overview
Initial situation
The Tabular Data Stream protocol (TDS) gives you the ability to establish a direct
connection with a Microsoft SQL server. Using TDS, you can log in to an SQL
server database and transmit SQL instructions. In this way it is possible to read
data from the database, or send them to the database for storage.
This application example demonstrates how a SIMATIC S7-1500 establishes a
connection to a Microsoft SQL server via TDS and sends data to a database using
"Open User Communication blocks" (TCON, TSEND, TRCV and TDISCON).
Overview of the application example

The figure below provides an overview of the application example:
SIMATIC S7-1500
SQL
SQL Database
PROFINET / IE

Article ID: 109779336, V1.0, 05/2020 4
1 Introduction
1.2 Principle of operation

The following figure shows the principle of operation, structure and states of the
function block "LSql_Microsoft".
Figure 1-1
Configure
connection
Connect
Establish error
Error
connection
Connection fully
Connected
established
Execute
Formulate SQL
instruction
Transmit SQL
Executed
instruction
Disconnect
Terminate connection Disconnected

Article ID: 109779336, V1.0, 05/2020 5
1 Introduction
1.3 Components Used

The following hardware and software components were used to create this
application example:
Table 1-1
Components Quantity Item number Note
STEP 7 Professional V16 1 6ES7822-1AA06-0YA7 Engineering system
SIMATIC S7-1500 1 6ES7511-1AK01-0AB0 Alternatively, you can
CPU 1511-1 PN use any other
SIMATIC S7-1500 or
ET 200SP CPU of
firmware V2.5 or
later.
This application example consists of the following components:

Table 1-2
Components File name Note
Project 109779336_SQL_S7_1500_CODE_V10.zip This zipped file
contains the STEP 7
project of the
application example
for the S7-1500.

Documentation 109779336_SQL_S7_1500_DOC_en_V10.pdf This document.

Article ID: 109779336, V1.0, 05/2020 6
2 Engineering
2 Engineering
2.1 Interface description
Function description
The function block "LSql_Microsoft" emulates the TDS protocol on the basis of
"Open User Communication blocks". It facilitates the following actions:
• Logging in to a Microsoft SQL server database (enable, connSettings,
loginInformation)
• Transmitting SQL instructions (sqlCommand, executeSqlCommand)
• Archiving received data (enableArchive, db, dbmax)
Internally, the block works with helper blocks. They are not explained here in more
detail.
Block interface
The following figure shows the interfaces of the function block "LSql_Microsoft" and
the associated data types.
Figure 2-1: LSql_Microsoft

LSql_Microsoft
Bool enable valid Bool
TCON_IP_v4 connSettings busy Bool
"LSql_typeLoginInformation" loginInfo error Bool

executeSqlCo
Bool status Word
mmand
String sqlCommand diagnostics "LSql_typeDiagnostics"
enableArchiv lastArchiveEl
Bool DInt
e ement
sqlCommand
DB_ANY db Bool
Done
DInt dbmax
Table 2-1: Parameter of LSql_Microsoft

Name P type Data type Comment
enable IN Bool TRUE: Enable functionality of FB
connSettings IN TCON_IP_v4 settings for connection to database
loginInfo IN "LSql_typeLoginInformation" used for logging on to database
executeSqlCommand IN Bool TRUE: execute sql command once
sqlCommand IN String sql command to execute when
executeSqlBatch = TRUE
enableArchive IN Bool TRUE: archive all answers of Trcv
db IN DB_ANY DB to archive Trcv answers in
dbmax IN DInt number of the element in db
valid OUT Bool TRUE: Valid set of output values
available at the FB

Article ID: 109779336, V1.0, 05/2020 7
2 Engineering
Name P type Data type Comment

busy OUT Bool TRUE: FB is not finished and new
output values can be expected
error OUT Bool TRUE: An error occurred during the
execution of the FB
status OUT Word 16#0000 - 16#7FFF: Status of the FB,
16#8000 - 16#FFFF: Error identification
diagnostics OUT "LSql_typeDiagnostics" Diagnostics information of FB (optional)
lastArchiveElement OUT DInt last Element in archive array that FB
has written to
sqlCommandDone OUT Bool TRUE: last command triggered with
input executeSqlCommand was
executed succesfully
Table 2-2: Parameter of LSql_typeLoginInformation

Name Type Value Comment
hostName String DEF_VAL optional: name of local host
userName String DEF_VAL mandatory: username for login to database
password String DEF_VAL mandatory: password for login to database
appName String DEF_VAL optional: name of app connectiong to database
serverName String DEF_VAL mandatory: servername of database
libraryName String DEF_VAL optional: name of ui

local String DEF_VAL optional: language used in ui
databaseName String DEF_VAL mandatory: database to read or write from
sspi String DEF_VAL optional / not supported: encryption via sspi
attachDbfile String DEF_VAL optional: filename to add at transfer
changePassword String DEF_VAL optional: new password if old one shold be changed
Table 2-3: Parameter of LSql_typeDiagnostics

Name Type Value Comment
status Word DEF_VAL Status of the Block or error identification when error occurred
subfunctionStatus Word DEF_VAL Status or return value of called FB's, FCs and system blocks
stateNumber DInt DEF_VAL State in the state machine of the block where the error occurred

Article ID: 109779336, V1.0, 05/2020 8
2 Engineering
2.2 Integration into the user project

Requirements
The following requirements apply to the use of the application example:
• S7-1500 firmware V2.5 or later
• Microsoft SQL server is fully configured
• S7-1500 and Microsoft SQL server are in the same subnet.
Note Under the following conditions, this module is also functional with an S7 1200
from firmware V4.4 or higher:
• Do not use the archive functionality

• Comment out program line 386 of the "LSql_Microsoft" block. To do this,
open the block and click on "Edit type". Now find program line 386 with the
"WriteToArrayDB" instruction and comment it out.
Restrictions
The following restrictions apply for this application example:
• The application example only works with the tested hardware and software
versions.
• Using Open User Communication with an S7-1500, a maximum of 65536 bytes
per command can be sent or received.
• The block "LSql_Microsoft" may be called no more than once per Microsoft
SQL server.

Article ID: 109779336, V1.0, 05/2020 9
2 Engineering
Integrating project components into the user project

Copy the following project components into your TIA Portal project:
• "LSql" block folder
• Data block "SqlConfig"
• Data block "Archive"
• PLC data types folder
Here, working in the "Reference projects" view is recommended.
Figure 2-2

Article ID: 109779336, V1.0, 05/2020 10
2 Engineering
Interconnecting the parameters of the "LSql_Microsoft" block

Call the block "LSql_Microsoft" in a cyclic block, e.g. "Main [OB1]" and interconnect
the inputs and outputs as seen in the following figure.
Figure 2-3
Note The wiring of the parameters described here is an essential requirement for
operating the block. In this application example, data are transmitted from the
controller to storage in the SQL server. It is not necessary to use an archive to
do this.
The connection settings and login information must be cleared on the SQL
server side in order to establish the connection. Further information on this topic
can be found in chapter Settings in Microsoft SQL Server Express.

Article ID: 109779336, V1.0, 05/2020 11
2 Engineering
2.3 Operation of LSql_Microsoft

The block LSql_Microsoft is controlled via the inputs "enable" and
"executeSqlCommand".
Establishing and terminating connection

The "enable" input controls the process of establishing and terminating a
connection to the SQL server. "enable" must have the value "TRUE" as long as
SQL instructions must be transmitted to the SQL server. If "enable" is set to
"FALSE" then the connection to the SQL server is terminated.
In order to successfully establish a connection, the following parameters must be
set in the data block "SqlConfig". The unfilled parameters are optional.

Article ID: 109779336, V1.0, 05/2020 12
2 Engineering
Table 2-4
Parameter Note
1. IP address and port of the SQL Default port for Microsoft SQL server is 1433.
server
2. SQL server login information See Settings in Microsoft SQL Server Express
3. Name of SQL server In this application example: SQLEXPRESS
4. Name of the database of the SQL An SQL server can contain multiple databases.
server Use this parameter to specify which database
you wish to connect to.
Transmitting SQL instructions

You will formulate and store an SQL instruction at the sqlCommand input. Once the
controller has established a connection to the SQL server, you can transmit the
instruction to the SQL server with a positive edge at the executeSqlCommand
input. The parameter "doneSqlCommand" switches to "TRUE" if the command was
successful.
In this application example the SQL instruction "insert into PLCDATA values
(5,6,7)" is specified. PLCDATA is a database table of the database
S71500SQLDB. The following figure shows this database table and the columns of
this table.
Figure 2-4

Article ID: 109779336, V1.0, 05/2020 13
2 Engineering
You can add a new row to this database table with "insert into". "Values" specifies
the value to be entered in the first (5), second (6) and third (7) column.
The SQL instruction is transmitted to the database with a positive edge at the input
"executeSqlCommand". The following figure shows the contents of the table
"PLCDATA" after this SQL instruction is executed.
Figure 2-5

Article ID: 109779336, V1.0, 05/2020 14
2 Engineering
2.4 Error handling

If the "LSql_Microsoft" has an error, inspect the output parameters "error", "status"
and "diagnostics". "Error - TRUE" signals that an error occurred during processing.
The "status" provides unambiguous information on the status of the block.
"diagnostics" gives you detailed status and diagnostic information from
subfunctions which the "LSql_Microsoft" block uses internally.
Per the status concept of the SIMATIC programming style guide used, the
parameters "error" and the highest-value bit (MSB) of "status" (bit 15) are identical.
The remaining bits are used for an error code which points unambiguously to the
cause. The error codes are also stored as constants in the local data of the block.
Status and error message HEX Codes

The following table describes the most important aspects of the status and error
messages from the block "LSql_Microsoft".
Table 2-5
Constant name/error message Value Meaning
STATUS_NO_CALL 7000 No job being currently processed
STATUS_FIRST_CALL 7001 First call after incoming new job (rising edge
at "enable")
STATUS_SUBSEQUENT_CALL 7002 Subsequent call during active processing

without further details
STATUS_TDISCON_SUCCESSFUL 7011 status from TDISCON called successfully
ERR_IN_BLOCK_OPERATION 8001 Error: Wrong operation of the function block
ERR_TDISCON_REMOTE_TERMINATION 80A3 Error: TDISCON, The connection is already
terminated or doesn't exist
ERR_PARAMETERIZATION 8200 Error during parameterization
ERR_PROCESSING_EXTERN 8400 Error when processing from outside (e. g.
wrong I/O signals)
ERR_UNDEFINED_STATE 8600 Error due to an undefined state in state
machine
ERR_DISCONNECT 8601 Error when calling TDISCON
ERR_CONNECT 8602 Error when calling TCON
ERR_PRELOGIN 8603 Error while executing prelogin
ERR_LOGIN 8604 Error while executing login
ERR_TRCV 8605 Error when calling TRCV
ERR_ARCHIVE 8606 Error: archiving in db
ERR_PRELOGIN_DATA 8607 Error when converting prelogin data to word
ERR_LOGIN_DATA 8608 Error when converting login data to word
ERR_SQLBATCH_DATA 8609 Error when converting SQL command data
to word

Article ID: 109779336, V1.0, 05/2020 15
3 Useful information
3.1 Microsoft SQL Server Express basics

Microsoft SQL Server Express
The Microsoft SQL server is a high-performance database management system for
SQL databases. The free Express version is designed for desktop and server
applications. It supports up to 10 gigabytes of storage per database.
The 2017 version can be downloaded here: https://www.microsoft.com/en-
US/download/details.aspx?id=55994
Microsoft SQL Server Management Studio

The free Microsoft SQL Server Management Studio provides tools for configuring,
monitoring and managing instances or SQL Servers and databases. It makes it
possible to send queries and scripts to databases in the form of SQL instructions.
In this way you can enter new data to the database table, or read existing data.
Version 18 can be downloaded here: https://docs.microsoft.com/en-
us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15
TDS – Tabular Data Stream protocol

The Tabular Data Stream protocol is a protocol on the application layer (layer 7) of
the ISO/OSI reference model. It facilitates interaction with a Microsoft SQL server,
including authentication and encryption of communication. After successfully
logging in to the SQL server, SQL instructions can be exchanged with the server's
databases using this protocol. Data are transported over TCP/IP.
The Tabular Data Stream is described extensively in the Microsoft Technical
Documentation: https://docs.microsoft.com/en-
us/openspecs/windows_protocols/ms-tds/b46a581a-39de-4745-b076-
ec4dbb7d13ec

Article ID: 109779336, V1.0, 05/2020 16
3.2 Settings in Microsoft SQL Server Express

Logging in to the SQL server
The login to a Microsoft SQL Server Express database via the SQL server
authentication mode must be authorized in the database settings. This is required
in order to log in to the database with username and password via the TDS
protocol. The necessary steps for this are described below.
1. Start the Microsoft SQL Server Management Studio.
2. Open the properties of the SQLEXPRESS instance.
3. In the security settings, activate the "SQL Server Authentication Mode" and
confirm this change with "OK".

Article ID: 109779336, V1.0, 05/2020 17
4. Create a user account under "Security > Logins > New Login…".
5. Assign a user name and select "SQL Server authentication". Set a password
and confirm the entries with "OK".
Note Make sure that this user receives the permissions to access this database.

Article ID: 109779336, V1.0, 05/2020 18
Port authorization in the SQL server
Note Port 1433 is the default port for Microsoft SQL server databases.
Note With a firewall active on the PC with the Microsoft SQL server database, the TCP
port "1433" must be allowed in the firewall for incoming connections.
A port authorization must be set up in the SQL server so that the SQL server is
reachable on the network. The necessary steps are described below.
1. Start the "Microsoft SQL Server Configuration Manager" and navigate to the
protocols for "SQLEXPRESS". Double click to open the TCP/IP settings.
2. Enable TCP/IP.

Article ID: 109779336, V1.0, 05/2020 19
3. Under IP3, configure the IP address of the network interface and the port 1433.
Enable the interface and confirm the settings with "OK".
4. Restart the SQL server service for the changes to take effect.

Article ID: 109779336, V1.0, 05/2020 20
4 Appendix
4 Appendix
4.1 Service and support
Industry Online Support
Do you have any questions or need assistance?
Siemens Industry Online Support offers round the clock access to our entire
service and support know-how and portfolio.
The Industry Online Support is the central address for information about our
products, solutions and services.
Product information, manuals, downloads, FAQs, application examples and videos
– all information is accessible with just a few mouse clicks:
support.industry.siemens.com
Technical Support
The Technical Support of Siemens Industry provides you fast and competent
support regarding all technical queries with numerous tailor-made offers
– ranging from basic support to individual support contracts. Please send queries
to Technical Support via Web form:
www.siemens.com/industry/supportrequest
SITRAIN – Training for Industry

We support you with our globally available training courses for industry with
practical experience, innovative learning methods and a concept that’s tailored to
the customer’s specific needs.
For more information on our offered trainings and courses, as well as their
locations and dates, refer to our web page:
www.siemens.com/sitrain
Service offer
Our range of services includes the following:
• Plant data services
• Spare parts services
• Repair services
• On-site and maintenance services
• Retrofitting and modernization services
• Service programs and contracts
You can find detailed information on our range of services in the service catalog
web page:
support.industry.siemens.com/cs/sc
Industry Online Support app

You will receive optimum support wherever you are with the "Siemens Industry
Online Support" app. The app is available for Apple iOS, Android and Windows
Phone:
support.industry.siemens.com/cs/ww/en/sc/2067

Article ID: 109779336, V1.0, 05/2020 21
4 Appendix
4.2 Links and literature

Table 4-1
No. Subject
\1\ Siemens Industry Online Support
https://support.industry.siemens.com
\2\ Link to the article page of the application example
https://support.industry.siemens.com/cs/ww/en/view/109779336
\3\
4.3 Change documentation

Table 4-2
Version Date Change
V1.0 05/2020 First edition

Article ID: 109779336, V1.0, 05/2020 22

Connecting An S7-1500 To An SQL Database

Uploaded by

Copyright:

Available Formats

Connecting An S7-1500 To An SQL Database

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Connecting An S7-1500 To An SQL Database

Uploaded by

Copyright:

Available Formats

How do you connect an S7-1500 PLC to a SQL database?

How do you connect an S7-1500 PLC to a SQL database?

What steps are required to configure the SQL server for connections?

What steps are required to configure the SQL server for connections?

Connecting an

Microsoft SQL with S7-1500

Microsoft SQL with S7-1500

Overview of the application example

Microsoft SQL with S7-1500

1.2 Principle of operation

Terminate connection Disconnected

Microsoft SQL with S7-1500

1.3 Components Used

This application example consists of the following components:

for the S7-1500.

Microsoft SQL with S7-1500

Figure 2-1: LSql_Microsoft

Bool enable valid Bool

TCON_IP_v4 connSettings busy Bool

"LSql_typeLoginInformation" loginInfo error Bool

Table 2-1: Parameter of LSql_Microsoft

Microsoft SQL with S7-1500

Name P type Data type Comment

Table 2-2: Parameter of LSql_typeLoginInformation

libraryName String DEF_VAL optional: name of ui

Table 2-3: Parameter of LSql_typeDiagnostics

Microsoft SQL with S7-1500

2.2 Integration into the user project

• Do not use the archive functionality

Microsoft SQL with S7-1500

Integrating project components into the user project

Microsoft SQL with S7-1500

Interconnecting the parameters of the "LSql_Microsoft" block

Microsoft SQL with S7-1500

2.3 Operation of LSql_Microsoft

Establishing and terminating connection

Microsoft SQL with S7-1500

Transmitting SQL instructions

Microsoft SQL with S7-1500

Microsoft SQL with S7-1500

2.4 Error handling

Status and error message HEX Codes

STATUS_SUBSEQUENT_CALL 7002 Subsequent call during active processing

Microsoft SQL with S7-1500

3.1 Microsoft SQL Server Express basics

Microsoft SQL Server Management Studio

TDS – Tabular Data Stream protocol

Microsoft SQL with S7-1500

3.2 Settings in Microsoft SQL Server Express

Microsoft SQL with S7-1500

Microsoft SQL with S7-1500

Port authorization in the SQL server

Microsoft SQL with S7-1500

Microsoft SQL with S7-1500

SITRAIN – Training for Industry

Industry Online Support app

Microsoft SQL with S7-1500

4.2 Links and literature

4.3 Change documentation