DBCON Connections not working after SAP SQL Server Upgraded to 2012

[Firoz_Ashraf]

When we upgraded our SAP Server Database from SQL Server 2008 to SQL Server 2012, the DBCON connection to external SQL Servers stopped working:

 

Issue No. 1 – Connecting to SQL Server 2000 was giving the following error:

                    "1- SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]"

External System	DBCON Connection String
Time Punch (SQL Server 2000)	MSSQL_SERVER=:BETAServer MSSQL_DBNAME=BETADb

Solution:

Since the DBCON / Multiconnect uses the Microsoft SQL Server Native Client Software (SNAC) to establish a connection to the remote SQL Server instance we have to install the SQL Native Client 2008 on our SAP Server as a workaround for connection to external SQL Server 2000 as per SAP Note 1781460.

Hence, as per SAP Note we did the following on our SAP Server and the DBCON connection started working:

1. Installed the client libraries for SQL Server 2008 (R2)


2. Created a system DSN using Control Panel -> Administrative Tools -> Data Sources (ODBC) specifying  the SQL Server 2008 native driver and without selecting the option to perform translation for character data.

3.   Added the following to the end of field CON_ENV in table DBCON for the connection which we are using:

MSSQL_DSN=ZDSN2000

So the new connection string now will be:

MSSQL_SERVER=:BETAServer MSSQL_DBNAME=BETADb MSSQL_DSN=ZDSN2000

Issue No. 2 – Connecting to SQL Server 2008 was giving the following error:

                    "53  Named Pipes Provider: Could not open a connection to SQL Server [53]."

External System	DBCON Connection String
Shop Floor System (SQL Server 2008)	MSSQL_SERVER=:APLHAServer MSSQL_DBNAME=ALPHADb

Solution:

After going through SAP Note 738371 we tried to remove the colon from the connection string having the server name i,e

MSSQL_SERVER=:

and it worked successfully !!

Hence, the new connection string will be:

MSSQL_SERVER=APLHAServer MSSQL_DBNAME=ALPHADb

 

Following is the 'colon table' that I have prepared based on my experience on DBCON during SQL Updgrade.

SAP SQL Server	Use of Colon in Connection string in DBCON for external SQL Connection
Before 2008	With colon
2008	With and withoutcolon both will work. Note that SQL Server 2008 R2 SP2 (10.50.6000.34) will work only without colon
2012	Without colon

Regards,

Firoz.