SAP Basis Tech Supports

Symptom
Some connection errors may occur when trying to connect to a remote
SQL Server using multiconnect.

Reason and Prerequisites
Some configuration problems in setting up a DBCON entry for multiconnect to SQL Server are preventing a reliable connection.
The application server(s) where a DBCON connection is used must be running Windows, and they must have the MS SQL Server DBSL library dbmssslib.dll installed in the run directory. If your database platform is not SQL Server, then this DLL must be downloaded from the SAP Service Marketplace for your R/3 kernel version.
Solution
There are three fields in the DBCON table that are important for the purposes of connecting to a SQL Server:
1. USER_NAME
This field should contain the name of a SQL Server user which can be used to connect to SQL Server using “SQL Server Authentication”. IMPORTANT: The user name is case sensitive. If left blank, then “Windows Authentication” is automatically used. It is important to remember that when using “Windows Authentication”, the user running the R/3 service is the user that must have access to the remote server. This user is usually <DOMAIN>\SAPServiceSID. You can see which user runs R/3 by checking Control Panel -> Administrative Tools -> Services, double-click on the SAP<SID> service and choose the logon tab.
2. PASSWORD
This is the password for the SQL Server user using “SQL Server Authentication”. The password is also case sensitive of course.
3. CON_ENV
This field is used to set further connect options. These options are set in a similar way to environment variables, separated by spaces. Some of the options that can be set are described below:a) MSSQL_SERVER=<server_name>
This option must be included. It specifies a sql server name or the name of a SQL Server named instance. It can also refer to an alias name defined in the SQL Server Client Network Utility.
Sometimes it’s necessary to control which network protocol is used for the connection. There are two ways to do this, aliases and special server name prefixes. When defining aliases using the SQL Server ClientNetwork Utility, you will explicitly choose a network protocol (tcp/ip or named pipes). The same alias name must be defined on all application servers that might need to use the DBCON connection. This makes it a bit more difficult to maintain.
Another option is to use special server name prefixes. They can be
set as follows:
MSSQL_SERVER=np:<server_name>
forces the use of named pipes.
MSSQL_SERVER=tcp:<server_name>
forces the use of tcp/ip.
If no prefix is used, then R/3 will actually insert its own prefixes based on the following rule:
Connection across network, use tcp:<server_name>
Local connection, use np:<server_name>
If a connection cannot be made using the prefixes after three retries each of which times out in 20 seconds, R/3 will then remove the prefix and use only <server_name>. You can stop R/3 from inserting it’s own prefixes by using
MSSQL_SERVER=:<server_name>
This will speed up the connection a lot if for example tcp:<server_name> doesn’t work but <server_name> does. This option should always be used
when using aliases.
b) MSSQL_DBNAME=<dbname>
This causes the connection to issue a “use <dbname>” statement once a successful connection is made. If the dbname is wrong, the connection will be in the master database. No user tables will be found there.
c) MSSQL_SCHEMA=<user_or_schema_name>
The system will initially connect using the SQL login specified in the USER field described above. If this MSSQL_SCHEMA parameter is set then the system will issue a setuser ‘<user_or_schema_name>’ to change the context to the indicated schema. In many cases this will be the three letter SAP schema (equal to the system ID in lower case).
d) MSSQL_CONN_TIMEOUT=<seconds>
If the system is running the ODBC DBSL version (7.00 basis and later), then this parameter can be set to achieve better control of the time taken to attempt a connection to the remote server.
e) OBJECT_SOURCE=<user_or_schema_name>
This parameter is used in the SAP monitoring applications starting with basis 6.40. It doesn’t affect the way a connection is made, it’s only used to indicate to the monitoring programs where the sap tools for monitoring are stored (stored procedures and functions with names like sap_*). This parameter should generally not be used unless the DBCON entry is used and maintained in the monitoring infrastructure.
Maintaining DBCON
The DBCON table is in the R/3 default database and can be maintained using the table maintenance tool (transaction SM30) or, as of Release 4.6, using transaction DBCO. This is described in general terms in note 323151.
In Release 6.40, the MS SQL Server porting group made a new method of maintenance available for maintaining SQL Server DBCON entries. This method is described in the on-line documentation available on help.sap.com under the SAP NetWeaver area. The documentation can be found in a document titled “Monitoring Remote SQL Server Databases”. The path in the on-line documentation is:
SAP NetWeaver
Application Platform (SAP Web Application Server)
Databases
SAP/MS SQL Server 2000 DBA in CCMS
Database Management Tools
SAP/SQL Server Database Monitor
Monitoring Remote SQL Server Databases
You can quickly access the maintenance of DBCON for SQL Server in basis version 6.40 or later by using st04, then click on the “Change connection data” button on the top right hand side, and then choose Go To -> Maintain DBCON. This will allow you to create DBCON entries for SQL Server without having to format the CON_ENV connect string manually.The details for how to use this maintenance screen are in the on-line documentation.
Troubleshooting:
1. Always test your connection using either osql or the Query Analyzer from all application servers that may be used. Testing with “SQL Server Authentication” is easy. Using “Windows Authentication” is a bit more difficult. Often you cannot logon as NT user DOMAIN\SAPServiceSID. But usually user DOMAIN\sidadm has the same privileges, and therefore you can test by logging in as sidadm, and then execute
osql -E -S<server_name>
Make sure <server_name> matches the MSSQL_SERVER setting, including the prefix (except the empty : prefix). Once you are able to connect enter “use <dbname>” where dbname matches the MSSQL_DBNAME setting.
2. You can test a connection using the following abap test program: REPORT ZTESTDBCON.
data: DBN(128).
EXEC SQL.
CONNECT TO ‘TST’
ENDEXEC.
EXEC SQL.
SET CONNECTION ‘TST’
ENDEXEC.
EXEC SQL.
SELECT db_name() INTO BN FROM SVERS
ENDEXEC.
WRITE: / ‘current database name’, DBN.
Substitute TST by your DBCON name (the CON_NAME from DBCON). If SVERS doesn’t exist in your database, then use any table name that does exist.This will allow you to easily monitor errors when connecting.
3. If a connection fails or takes a very long time, the first place to look for errors is in the developer trace files (SID\DVEBMGS00\work\dev_w??). The error messages will indicate which network protocol was attempted:
[DBNMPNTW]ConnectionOpen
for example means that named pipes were being used. However the trace files don’t show a lot of detail beyond that. To get a more detailed trace you can do the following:a) se38 -> Run program RSMSS_DBSL_PROFILE_SWITCH. Choose to set profiling ON.b) Run the program that connects to the remote server and wait for it to fail.c) Run program RSMSS_DBSL_PROFILE_SWITCH again and turn profiling OFF. NEVER leave the profiling on for extended periods.
Now new trace file(s) will exist in SID\DVEBMGS00\work named dbsl_w??. These files contain much more detail about the server name, and other connect options being used.