HowTO: create SQL Server DB connection on SAP

Short HowTO story about creating database connection from SAP to the third party database systems. In our case it will be Microsoft SQL Server.

HowTO: PreSteps

Create SQL Server login with appropriate authorization

Enter login name — password and under User Mapping assign database authorization.

You can do same with TSQL query.

USE [master]
GO
CREATE LOGIN [testing_sap] WITH PASSWORD=N'123456', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [SAPTEST]
GO
CREATE USER [testing_sap] FOR LOGIN [testing_sap]
GO
USE [SAPTEST]
GO
ALTER ROLE [db_owner] ADD MEMBER [testing_sap]
GO

Install unixODBC and msodbcsql on SAP application server.

curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo #add repo
yum remove unixODBC-utf16 unixODBC-utf16-devel #remove existing packages
yum install msodbcsql-13.1.4.0-1 #install msodbcsql
yum install unixODBC-devel # install unixODBC
ln -s /usr/lib64/libodbc.so.2.0.0 /usr/lib64/libodbc.so.1
ln -s /usr/lib64/libodbcinst.so.2.0.0 /usr/lib64/libodbcinst.so.1
odbcinst -q -d -n "ODBC Driver 13 for SQL Server" #Check config

HowTO: create connection

T-code: dbacockpit → DB Connections →Add

Enter you SQL Server connection information and save

Select connection and clieck on Test button. See result

Now you can use TEST_DATABASE_CONNECTION.