Search code examples
sql-serverdatabaseodbcsybaselinked-server

Sybase Linked Server in MS SQL failes to connect but ODBC Test connection succeeded

In our Database Server, we have installed below version of Microsoft SQL:

Microsoft SQL Server 2016 (SP2-CU2-GDR) (KB4458621) - 13.0.5201.2 (X64) Aug 18 2018 07:38:15 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)

In the same server, we also installed Sybase 16 Driver

Using the Adaptive Server Enterprise, we created ODBC 64-bit Connection (since MS SQL is 64-bit) and after Testing the connection, the prompt shows that the connections and the credentials are good.

I then created a linked server in MS SQL by using the following script

not real values for credentials of course

DECLARE @ls_servername varchar(128) = N'SybaseSERVER'
DECLARE @ls_serverproduct varchar(128) = N'sybase'
DECLARE @ls_provider varchar(128) = N'MSDASQL'
DECLARE @ls_datasource varchar(128) = N'SybaseSERVER'
DECLARE @ls_catalog varchar(128) = N'TestDB'
DECLARE @ls_providerstr varchar(max) = NULL
DECLARE @ls_username varchar(128) = N'*TestUser*'
DECLARE @ls_password varchar(128) = N'*TestPass*'


--Add linked server
EXEC sp_addlinkedserver 
    @server=@ls_servername
    ,@srvproduct=@ls_serverproduct
    ,@provider=@ls_provider
    ,@datasrc=@ls_datasource
    ,@catalog=@ls_catalog
    ,@provstr=@ls_providerstr

--Add credentials
EXEC sp_addlinkedsrvlogin 
    @rmtsrvname=@ls_servername
    ,@useself=N'False'
    ,@rmtuser=@ls_username
    ,@rmtpassword=@ls_password
GO

Using the above set up, I was able to query successfully in our DEV Database Server SELECT * FROM SybaseSERVER.TestDB.dbo.SampleTable

However, using the same setup in out Staging Database Server, I encountered an error:

OLD DB provider "MSDASQL" for linked server "SybaseSERVER" returned message "[SAP][ASE ODBC Driver]Client unable to establish a connection".

But the ODBC 64-bit connection shows successful when Testing the connection.

I already checked the setup and I'm sure they're both the same in terms of installation of MS SQL and Sybase Driver. The PATH environment variables don't differ much when checking both.

What would be the possible cause of the issue in our Staging Server? And what could be the possible solution?

Solution

  • This is what our DBA did to make it work

    1. Set the remote access to 1 (both config_value and run_value)
    2. Restart the SQL Service.

    I'm not sure if the point 1 is connected but according to our DBA, that's what he did. But for now, that's our first action in case we experience the issue again on other servers.