SQL Server Connection Issues: Connection timeout or connection error.
This is a description of the connection mechanisms using R/3 with dblib and oledb, connecting to SQL Server 7.0 and later. This tutorial could also be useful in troubleshooting various connection problems.
R/3 executables (kernel and standalone), need three variables to connect to SQL Server:
1. SERVER - name of SQL Server (default or named instance).
2. DBNAME - name of database containing the R/3 data.
3. SCHEMA - namespace context (since release 4.6c). Must be set when using multiple R/3 systems within the same physical database (MCOD- or schema installation).
For non-MCOD-installations, this variable must either not be set or set to dbo.
This is enough information to make a connection using "Windows Authentication". The connections are made using the NT user ID that owns the session.
For R/3 it's usually SAPService. R/3 releases older than 4.5b also need USER and PASSWD because SQL Server authentication is used. There is typically also an administrative user named adm.
Usually the DBNAME information is not needed because the user's default database is already set to the correct value. Use Enterprise Manager to view the settings of your users (SAPService and adm).
These parameters are specified in the following ways:
1. For the R/3 system kernel, the R/3 profiles are used:
- SERVER is set by using SAPDBHOST in the DEFAULT.PFL profile.
When using a named instance then profile parameter
dbs/mss/server = should be used instead.
- DBNAME usually inherits the setting of the SAPSYSTEMNAME
profile parameter. It can also be set in the instance profile using dbs/mss/dbname.
- SCHEMA is set using dbs/mss/schema in the default profile.
This variable must be set if the system is an MCOD system.
This allows multiple R/3 system to share the same database.
If there is one MCOD system, all R/3 systems in the same database must be MCOD systems.
For MCOD-installations SCHEMA is always equal to the in lowercase letters.
For non-MCOD-installations SCHEMA is either not set or set to dbo.
2. For standalone programs environment variables are used.
- SERVER is set using environment variable MSSQL_SERVER
- DBNAME is set using MSSQL_DBNAME
- SCHEMA is set using MSSQL_SCHEMA.
In addition the environment variable "dbms_type" must be set to mss. Example (for an MCOD system on a named instance):
If dbs/mss/schema is not set, then MSSQL_SCHEMA must not be set.
Since 4.6D the dbs/mss/ is the preferred format for MSS profile parameters.
In 6.20 and in patches of 6.10 and 4.6d, some small changes were made:
1. The R/3 kernel ignores all environment variables, but a message is printed in the developer traces (dev_w??) if the corresponding variable is set to a different value.
Older releases use the environment variables and those values override the profiles.
2. dbs/mss/ should now be used exclusively. If older formats (dbs/oledb/.., rsdb/mssql/...) are used, they still work.
In 6.20 a "deprecation" message is printed in the dev traces.
All 6.10 systems should run with (at least) the 6.20 versions of the executables. So all 6.10 systems allow the recommended dbs/mss method.
The use of dbs/mss is also highly preferred when using the 4.6d kernel/dbsl. Any 4.6d patch including and after dbsl_lib patchlevel 437 (note 379572), which was delivered in february 2001 will support this method. This patch will appear like this in the lib_dbsl info file on the SAP Support Marketplace:
( 0.437) Prepare: several mySAP.com components in one DB (note 379572)
POSSIBLE CONNECTION PROBLEMS:
It's very important to be aware of which network layer libraries are used to connect to SQL Server. You can view and change the network library choices in the Client Configuration Utility. In R/3 we only recommend named pipes for a central system and tcp/ip for dialog instances.
Connections can be made to a local server (running on the same host as the client) by entering a blank server name. This will force the connection to be made over local named pipes. This is the fastest connection method possible.
If the R/3 application server is a central system (on the same host as the database), then the R/3 interface will attempt to make a connection using a blank server name. It does this by comparing the hostname with the contents of profile parameter dbs/mss/server. If it's the same then a blank (empty string) will be used to connect.
For R/3 releases up to and including 4.0B:
If the db server name is different from the appserver name, then the dbsl interface will try to ensure that a tcp/ip connection will be used. This is because tcp/ip is the most efficient network library when connecting between two machines. To make sure a tcp/ip netlib is used, dbsl will set registry key
(where host is the sql server name). This registry key will be set to "DBMSSOCN". In the dbsl interface for oledb (only 4.0x) it's "DBMSSOCN,%s,1433". Dbsl will substitute the db server name for %s.
NOTE: DBSL will NOT overwrite a registry entry that has already been filled in. This is very important if you want to manually control the way R/3 connects using the Client Configuration utility.
In version 4.5B and later, DBSL doesn't overwrite the registry anymore. The system administrator is therefore responsible for setting the correct protocol (TCP/IP over the network) for releases 4.5B and later.
The Client Configuration Utility can be used to manually change the registry entries for the client.
The Client Configuration Utility is included in all clients for SQL server. The user interface is a little different in 7.0 versus later releases. You can launch the program from the Microsoft SQL Server program group or if it's not there you can launch it directly using %WinDir%System32CliConfg.exe
R/3 4.0B ONLY:
When connecting in 4.0B there is a special mechanism for retrieving database passwords:
First a secure connection is attempted to the db server, a table containing user and password information is read from the database, and then the secure connection is closed. Then this information is used to make the normal connections to the sql server database.
The dbsl for oledb then creates two registry entries:
1. server_name set to DBMSSOCN,server_name,1433 (shows up as TCP/IP in the client network utility).
2. server_name_secure set to DBNMPNTW,server_namepipesqlquery
The secure connection (without username and password) is then made to the (named pipe) server_name_secure server name. All the other connections are made to server_name.
The secure connection means that the user that runs R/3 (SAPService) must have NT administrator privilege on the server machine to connect to the SQL Server.
If you want to change the way R/3 connects to SQL Server, you can simply use the client configuration utility to edit the entries in the "aliases" section.
You can "trick" R/3 into thinking it's on a separate machine by doing the following:
1. Set dbs/mss/server =dummy in the R/3 profile (or use any name you choose).
2. Create an entry in client configuration named dummy but use the real server name when you create the entry value:
DBNMPNTW,server_namepipesqlquery or DBMSSOCN,server_name,1433
Now R/3 will connect using the netlib you specify (named pipe or sockets respectively). You specify the "real" server name in the advanced registry values you create.
If you're using oledb, then an entry named dummy_secure will be created when R/3 starts with the value DBNMPNTW,dummypipesqlquery
You have to manually change that value to DBNMPNTW,server_namepipesqlquery
In some cases there are problems with making a connection using an empty server name. In that case you can use the trick described above as a temporary workaround.
Starting with 4.5B "secure" or "Windows NT Authentication" is used exclusively. Also no registry entries are modified as mentioned above. This means the DBA has the added responsibility of checking the network protocol and use tcp/ip for all connections from appservers not running on the same host as the database server. See note 208632.
TROUBLESHOOTING STANDALONE CONNECTION PROBLEMS:
If a standalone program such as R3trans.exe or tp.exe is having problems connecting, you can obtain further trace information by using a special environment variable called MSSQL_DBSLPROFILE.
SET MSSQL_DBSLPROFILE=1 r3trans -d
Now a file has been created named dbsl_w0 (or for 3.1i dbsl_p???) in your current directory. Examine this file to see if there is further information or hints as to where the problems is. Send this file to SAP Support if necessary.
TROUBLESHOOTING R/3 SYSTEM CONNECTION PROBLEMS:
You can also obtain dbsl_w?? trace files from the kernel by doing the following:
1. Add dbs/mss/dbsl_profile=1 in the instance profile _DVEBMGS??_, not DEFAULT.PFL.
2. Start R/3, wait a few minutes and then stop R/3.
3. Examine new dbsl_w?? files in usrsapDVEBMGS??work or send them to SAP Support.
4. IMPORTANT: Take dbs/mss/dbsl_profile out of the profile again.
Otherwise you will soon run out of diskspace.