How To Create Odbc Connection For Sybase

Yesterday I spent a lot of time with quite a simple problem: how to connect to a Sybase database server from SQL Server. It was a neverending story to search up tips and ideas and put them together. After a long time spent testing ideas, I found a simple solution. 🙂

All you need is (on your computer):

The ODBC driver for Sybase (something like SYBDRVODB64DLL) - for x64 bit OS
MicThe osoft OLE DB Provider for ODBC Drivers ( in other words [MSDASQL] in your MSSQL SSMS Object explorer/Server Objects/Linked servers/Providers)

Note: my OS and MSSQL configuration is 64-bit.

The idea is very simple. I have a Sybase driver on my operating system, and from it I create a System DSN. This DSN is what I pass to the MSDASQL driver in my SQL Server instance.

The first thing you have to do is install a Sybase driver on the operating system from which you will connect to the Sybase database. One way how to achieve this is here. Because Sybase is now owned by SAP systems, you need to have an account at SAP, which is a paid service. For our purposes, it is OK to use the evaluation version of ASE (as Sybase and its drivers are now under the ASE product). Use the "Get free trial" button to obtain the installation package. Once you start installation process, is was OK to install only the "Interactive SQL" and "SAP ASE ODBC Driver" items from the Custom installation menu.

To check if the driver is successfully installed, go to the Control Panel\All Control Panel Items\Administrative Tools\ODBC Data Sources (64-bit) and check if "Adaptive Server Enterprise" is listed under the drivers installed. There may be another type of Sybase data access driver, depending on the type of Sybase drivers you downloaded.

To create a System DSN, choose "System DSN" tab in "ODBC Data Sources (64-bit)", then tap the Add button. Choose an appropriate driver name, which in my case was "Adaptive Server Entreprise", and then tap the Finish button. A new setup window opens, where you type the name of your data source. Remember this name because you will need it in the Linked Server configuration. My example is with the Sybase server that is not broadcasting on a standard port (that mine is 2035), so I will use the port number of my Sybase server in the properties for the new System DSN.

In the image above you can see the settings. Fill up them according your configuration and tap the "Test Connection" button. In my case, the test failed, because of a problem with the code page. A possible solution is in the Advanced tab where the Communication Charset can be set to "Client Charset", shown below.

After a successful test connection, everything should be OK. Save the configuration by clicking the OK button. Now you should see something like screen below:

As I mentioned, remember the name of the System Data Source. In my case, this is SybExample.

Let's now open SQL Server Management Studio (SSMS) and setup a Linked Server. The simplest way is to use the script below, which will create linked server for you. Be sure you change the username and pswd values accordingly to access your Sybase system.

DECLARE @ls_servername varchar(128) = N'SybaseExample' DECLARE @ls_serverproduct varchar(128) = N'SybExample' DECLARE @ls_provider varchar(128) = N'MSDASQL' DECLARE @ls_datasource varchar(128) = N'SybExample' DECLARE @ls_catalog varchar(128) = N'' DECLARE @ls_providerstr varchar(max) = NULL DECLARE @ls_username varchar(128) = N'*username*' DECLARE @ls_password varchar(128) = N'*pswd*' --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

Now your new linked server has the name SybaseExample.

Remember one thing: in the SSMS Object Explorer, go to your SQL server instance\Server Objects\Linked Servers\Providers\MSDASQL, the right click on MSDASQL. In Properties , check "Allow Inprocess".

If you rather use SSMS interface to configure your Linked server, use these settings:

Product Name = @srvproduct = @ls_serverproduct
Data Source = @datasrc = @ls_datasource
at Security card in Linked server properties, choose last option - Be made using this security context. Remember to fill in the remote login and password.

The final Linked Server should look like this: