Name already in use
sql-docs / docs / connect / odbc / windows / features-of-the-microsoft-odbc-driver-for-sql-server-on-windows.md
- Go to file T
- Go to line L
- Copy path
- Copy permalink
11 contributors
Users who have contributed to this file
- Open with Desktop
- View raw
- Copy raw contents Copy raw contents
Copy raw contents
Copy raw contents
Features of the Microsoft ODBC Driver for SQL Server on Windows
Microsoft ODBC Driver 18.0 for SQL Server on Windows
The ODBC Driver 18.0 allows users to send long data types as max data types using the keyword LongAsMax to toggle the setting on or off. When enabled, the SQL types SQL_LONGVARCHAR, SQL_LONGVARBINARY, and SQL_LONGWVARCHAR will be sent as varchar(max), varbinary(max), and nvarchar(max) instead of text, image, and ntext respectively.
The connection encryption defaults have changed. The default value of Encrypt is now Yes, and the new keywords Optional and Mandatory have been introduced as synonyms for No and Yes, respectively. For connecting using the TDS 8.0 protocol, the Strict mode ( Encrypt=Strict ) has been added. In this mode, the server certificate is always verified ( TrustServerCertificate is ignored). A new keyword, HostnameInCertificate , can be used to specify the expected hostname found in the certificate if it differs from the specified server. HostnameInCertificate is usable in all encryption modes and is also applicable if the server-side Force Encryption option is enabled, which will cause the driver to verify the certificate in Optional or Mandatory modes unless disabled using TrustServerCertificate .
The ODBC Driver 18 includes SQLGetData extensions. When enabled, SQLGetData can be used to retrieve column data in any order within a row, including going backwards. The feature is provided for compatibility purposes and will significantly degrade performance and increase memory usage. It is highly recommended that application always access the columns in ascending order. To enable SQLGetData extensions one can either use the GetDataExtensions connection string attribute, or use the SQLSetConnectAttr function to set SQL_COPT_SS_GETDATA_EXTENSIONS to SQL_EN_ON .
Support for Windows ARM64 is included starting with ODBC Driver 18.2 and is currently a preview. The ARM64 driver is for 64-bit ARM64 applications only and does not include 32-bit ARM nor x86/amd64 emulation drivers.
Microsoft ODBC Driver 17.4 for SQL Server on Windows
The ODBC Driver 17.4 includes the ability to adjust TCP Keep-Alive settings. They can be modified by adding values to the Driver or DSN registry keys. The keys are located in HKEY_LOCAL_MACHINE\Software\ODBC\ for system data sources, and in HKEY_CURRENT_USER\Software\ODBC\ for user data sources. For DSN, the values need to be added to . \Software\ODBC\ODBC.INI\<DSN Name> and for the Driver to . \Software\ODBC\ODBCINST.INI\ODBC Driver 17 for SQL Server .
The values are REG_SZ and are as follows:
KeepAlive controls how often TCP attempts to verify that an idle connection is still intact by sending a keep-alive packet. The default is 30 seconds.
KeepAliveInterval determines the interval separating keep-alive retransmissions until a response is received. The default is 1 second.
Microsoft ODBC Driver 13.1 for SQL Server on Windows
The ODBC Driver 13.1 for SQL Server contains all the functionality of the previous version (11). It also adds support for Always Encrypted and Azure Active Directory authentication.
Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to SQL Server. An Always Encrypted enabled driver installed on the client computer achieves this security by automatically encrypting and decrypting sensitive data in the SQL Server client application. The driver encrypts the data in sensitive columns before passing the data to SQL Server. It also automatically rewrites queries so that the semantics to the application are preserved. Similarly, the driver transparently decrypts data stored in encrypted database columns that are contained in query results. For more information, see Using Always Encrypted with the ODBC Driver.
Azure Active Directory allows users, DBAs, and application programmers to use Azure Active Directory authentication. For more information, see Using Azure Active Directory with the ODBC Driver, and Connecting to SQL Database or Azure Synapse Analytics By Using Azure Active Directory Authentication.
Microsoft ODBC Driver 11 for SQL Server on Windows
The ODBC Driver for [!INCLUDEssNoVersion] contains all the functionality of the [!INCLUDEssNoVersion] Native Client ODBC driver that shipped in [!INCLUDEssSQL11]. For more information about SQL Server Native Client, see SQL Server Native Client Programming. The [!INCLUDEssNoVersion] Native Client ODBC driver is based on the ODBC driver that ships in the Windows operating system. For more information about that driver, see Windows Data Access Components SDK.
This release of the ODBC Driver for [!INCLUDEssNoVersion] contains the following new features:
bcp.exe -l option for specifying a login timeout
The -l option specifies the number of seconds before a bcp.exe login to [!INCLUDEssNoVersion] times out when you try to connect to a server. The default login timeout is 15 seconds. The login timeout must be a number between 0 and 65534. If the value supplied isn’t numeric or doesn’t fall into that range, bcp.exe generates an error message. A value of 0 specifies an infinite timeout. A login timeout of less than (approximately) 10 seconds isn’t reliable.
Driver-aware connection pooling
Asynchronous execution (notification method)
To ensure that applications remain connected to a Microsoft Azure SQL Database, the ODBC driver on Windows can restore idle connections. For more information, see Connection Resiliency in the Windows ODBC Driver.
In [!INCLUDEssNoVersion] Native Client, the -y0 option for sqlcmd.exe caused output to be truncated at 1 MB if the display width was 0.
Beginning in the ODBC Driver 11 for [!INCLUDEssNoVersion], there’s no limit on the amount of data that can be retrieved in a single column when -y0 is specified. sqlcmd.exe now streams columns as large as 2 GB ([!INCLUDEssNoVersion] data type maximum).
Another difference is that specifying both -h and -y0 now produces an error reporting that the options are incompatible. -h , which specifies the number of rows to print between the column headings and has never been compatible with -y0 , was ignored although no headers were printed.
-y0 can cause performance issues on both the server and the network, depending on the size of the data returned.
Microsoft ODBC Driver for SQL Server on Linux – by the SAP HANA Academy
At the SAP HANA Academy we are currently updating our tutorial videos about SAP HANA administration [SAP HANA Administration – YouTube].
One of the topics that we are working on is SAP HANA smart data access (SDA) [SAP HANA Smart Data Access – YouTube].
Configuring SDA involves the following activities
- Install an ODBC driver on the SAP HANA server
- Create an ODBC data source (for remote data sources that require an ODBC Driver Manager)
- Create a remote data source (using SQL or SAP HANA studio)
- Create virtual tables and use them in calculation views, etc.
As of SPS 11, the following remote data sources are supported:
- Apache Hadoop (Simba Apache Hive ODBC)
- Apache Spark
In the SAP HANA Administration Guide, prerequisites and procedures are documented for each supported data source, but the information is intended as a simple guide and you will need ‘to consult the original driver documentation provided by the driver manufacturer for more detailed information‘.
In this series of blogs, I will provide more detailed information about how perform activity 1. and 2,; that is, installing and configuring ODBC on the SAP HANA server.
The topic of this blog is the installation and configuration of the Microsoft ODBC driver for SQL Server on Linux.
Video Tutorial
In the video tutorial below, I will show you in less than 10 minutes how this can be done.
If you would like to have more detailed information, please read on.
Supported ODBC Driver Configurations
At the time of writing, there are two ODBC drivers for SQL Server available for the Linux (and Windows) platform: version 11 and 13 (Preview).
| Microsoft ODBC driver for SQL Server on Linux | SQL Server | OS (64-bit) | unixODBC | SAP HANA Smart Data Access support |
|---|---|---|---|---|
| Version 13 (Preview) | 2016, 2014, 2012, 2008, 2005 | RHEL 7, SLES 12 | 2.3.1 | Not supported |
| Version 11 | 2014, 2012, 2008, 2005 | RHEL 5, 6; SLES 11 | 2.3.0 | SQL Server 2012 |
For SAP HANA smart data access, the only supported configuration is Microsoft ODBC Driver 11 in combination with SQL Server 2012. Supported means that this combination has been validated by SAP development. It does not mean that the other combinations do not work; they probably work just fine. However, if you run into trouble, you will be informed to switch to a supported configuration.
Information about supported configurations is normally provided in the SAP Product Availability Matrix on the SAP Support Portal, however, so far only ASE and IQ are listed. For the full list of supported remote data sources, see SAP Note 1868209 – SAP HANA Smart Data Access: Central Note.
unixODBC
On the Windows platform, the ODBC driver manager is bundled together with the operating system but on UNIX and Linux this is not the case so you will have to install one.
The unixODBC project is open source. Both SUSE Linux Enterprise Server (SLES) and Red HatEnterprise Linux (RHEL) provide a supported version of unixODBC bundled with the operating system (RPM package). However, Microsoft does not support these bundled unixODBC packages for the Microsoft ODBC Driver Version 11 so you will need to compile release 2.3.0 from the source code. This is described below.
| unixODBC | Release Date | OS (64-bit) | Microsoft ODBC Driver |
|---|---|---|---|
| 2.3.4 (latest) | 08.2015 | ||
| 2.3.2 | 10.2013 | ||
| 2.3.1 | 11.2001 | RHEL 7, SLES 12 | Version 13 (Preview) |
| 2.3.0 | 04.2010 | Version 11 | |
| 2.2.14 | 11.2008 | RHEL 6 | |
| 2.2.12 | 10.2006 | SLES 11 |
System Requirements
First, you will need to validate that certain OS packages are installed and if not, install them (System Requirements).
This concerns packages like the GNU C Library (glibc), GNU Standard C++ library (libstdc++), the GNU Compiler Collection (GCC) to name a few, without which you will not get very far compiling software. Also, as the Microsoft ODBC Driver supports integrated security, Kerberos and OpenSSL libraries are required.
Installing the Driver Manager
Next, you will need to download and build the source for the unixODBC driver manager (Installing the Driver Manager).
- Connect as root
- Download and extract the Microsoft driver
- Run the script build_dm.sh to download, extract, build, and install the unixODBC Driver Manager
The build script performs the installation with the following configuration:
# export CPPFLAGS=”-DSIZEOF_LONG_INT=8″
# ./configure –prefix=/usr –libdir=/usr/lib64 –sysconfdir=/etc –enable-gui=no –enable-drivers=no –enable-iconv –with-iconv-char-enc=UTF8 –with-iconv-ucode-enc=UTF16LE”
# make
# make install
Note the PREFIX, LIBDIR and SYSCONFDIR directives. This will put the unixODBC driver manager executables (odbcinst, isql), the shared object driver files, and the system configuration files (odbcinst.ini and odbc.ini for system data sources) all in standard locations. With this configuration, there is no need to set the environment variables PATH, LD_LIBRARY_PATH and ODBCINSTINI for the login shell.
Installing the Microsoft ODBC Driver
Take a look again at the output of the build_dm.sh (print screen above). Note the passage:
PLEASE NOTE THAT THIS WILL POTENTIALLY INSTALL THE NEW DRIVER MANAGER OVER ANY
EXISTING UNIXODBC DRIVER MANAGER. IF YOU HAVE ANOTHER COPY OF UNIXODBC INSTALLED,
THIS MAY POTENTIALLY OVERWRITE THAT COPY.
For this reason, you might want to make a backup of the driver configuration file (odbcinst.ini) before you run the installation script.
- Make a backup of odbcinst.ini
- Run install.sh –install
The script will register the Microsoft driver with the unixODBC driver manager. You can verify this with the odbcinst utility:
odbcinst -q -d -n “ODBC Driver 11 for SQL Server”
Should the install have overwritten any any previous configuration, you either need to register the drivers with the driver manager again or, and this might be easier, restore the odbcinst.ini file and manually add the Microsoft driver.
For this, create a template file (for example, mssql.odbcinst.ini.template) with the following lines:
[ODBC Driver 11 for SQL Server]
Description=Microsoft ODBC Driver 11 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
Threading=1
Then register the driver with the driver manager using the command:
odbcinst -i -d -f mssql.odbcinst.ini.template
Create the data source and test the connection
Finally, we can register a data source with the driver manager. For this, create a template file and save it as mssql.odbc.ini.template.
You can give the data source any name. Here MSSQLTest is used, but for production systems, using the database name might be more sensible (spaces are allowed for the data source name).
Driver = name of the driver in odbcinst.ini or the full path to driver file
Server = host (FQDN); protocol and port are optional, if omitted tcp and 1433 will be used.
Database = database name (defaults to Master)
[MSSQLTest]
Driver = ODBC Driver 11 for SQL Server
Description = SQL Server 2012 test instance
; Server = [protocol:]server[,port]
; Server = tcp:mo-9e919a5cc.mo.sap.corp,1433
Server = mo-9e919a5cc.mo.sap.corp
Database = AdventureWorks2012
Register the DSN with the driver manager as System DSN using the odbcinst utility:
odbcinst -i -s -l -f mssql.odbc.ini.template
The -v [erbose] flag can useful in case the connection fails, as it will tell you, for example, that your password is incorrect. For more troubleshooting, see below.
System or User Data Source
It is up to you, of course, whether to register the data source as a system data source or a user data source. As the SAP HANA server typically is a dedicated database system, using only system data sources has two advantages:
- Single location of data source definitions
- Persistence for system updates
With the data sources defined in a single location, debugging connectivity issues is simplified, particularly when multiple drivers are used.
With the data sources defined outside of the SAP HANA installation directory, you avoid that your odbc.ini will be removed when you uninstall or update your system.
To register the DSN with the driver manager as User DSN using the odbcinst utility, connect with your user account and execute:
odbcinst -i -s -h -f mssql.odbc.ini.template
The difference is the -h (home) flag and not – l (local).
Test connection (same as when connecting to a system data source):
Note that when no user data source is defined, odbcinst will return a SQLGetPrivateProfileString message.
Troubleshooting
Before you test your connection, it is always a good idea to validate the input.
For the driver, use the “ls” command to verify that the path to the driver is correct.
For the data source, use the “ping” command to verify that the server is up and use “telnet” to verify that the port can be reached (1433 for SQL Server is the default but other ports may have been configured; check with the database administrator).
If you misspell the data source name, the [unixODBC] [Driver Manager] will inform you that the
Data source name not found, and no default driver specified
If you make mistakes with the user name or password, the driver manager will not complain but the isql tool will forward the message of the database server.
If the database server cannot be reached, for example, because it is not running, or because the port is blocked, isql will also inform you by forwarding the message from the database server. Note that the message will depend on the database server used. The information we get back from SQL Server is much more user-friendly then DB2, for example.
If the driver manager cannot find the driver file, it will return a ‘file not found’ message. There could be a mistake in the path to driver file.
Differences Between Drivers for ODBC Drivers
I was setting up the System DSN (64 bit) for my database in SQL server 2016 with Windows 10 64 bit pro. While I was asked to choose the driver to set up a data source, there are the following selections:
- ODBC Driver 13 for SQL Server
- SQL Server
- SQL Server Native Client 11.0
- SQL Server Native Client RDA 11.0
It seemed I can set up the data source with all of these drivers. Then which one should I choose in terms of speed and efficiency? What’s the difference between them?
5 Answers 5
ODBC Driver for SQL Server
ODBC is the primary native data access API for applications written in C,C++, PHP, Python and others for connecting to SQL Server.
It’s widely used in data integration scenarios.
Also, it’s preferred if you are developing application on Windows and deploying it to Linux .
SQL Server
The full name of SQL Server Driver is SQL Server ODBC Driver. It’s an old driver since sql 2000. You can use it to connect to SQL Server 2016, but you will not be able to access new features and functionality of SQL Server 2016
SQL Server Native Client
SQL Server Native Client is containing both the SQL OLE DB provider and SQL ODBC driver to support native connectivity to SQL Server and support all features of sql server 2016. It’s the best in windows environment
SQL Server Native Client RDA
Remote data access (RDA) in Microsoft SQL Server Compact 3.5 lets an application access data from a remote SQL Server database table.
It can also store, read, and update that data in SQL Server Compact 3.5, and then update the original SQL Server table.
How to configure a Linked Server using the ODBC driver
Microsoft Open Database Connectivity (ODBC) is an application programming interface (API) designed to access data from a different of database management systems (DBMS). ODBC is designed for relational data stores.
In this article, we will explain how to install the appropriate ODBC drivers for SQL Server, how to configure ODBC to connect to a SQL Server instance and how to create and configure a Linked Server using the ODBC driver and the MSDASQL provider to query tables on a SQL Server instance.
The diagram below explains the flow of connecting to a SQL Server database using the MSDASQL provider and a SQL Server ODBC driver.
MSDASQL allows OLE DB consumer applications to use ODBC drivers to connect to a data source. The provider transforms incoming OLE DB calls into ODBC calls, and transfers them on to the specified ODBC driver. After that, it retrieves results from the ODBC driver and formats those into OLE DB specific structures which the consumer has access to them.
Installing and configuring the Microsoft ODBC drivers for SQL Server
From this link, the Microsoft ODBC drivers for SQL Server can be downloaded and installed. Depending on which Windows operating system version (32-bit or 64-bit) is used, there are two versions of the Microsoft ODBC drivers for SQL Server that can be installed:
- x64\msodbcsql.msi is for the Windows 64-bit version
- x86\msodbcsql.msi is for the Windows 32-bit version
In case that the wrong version of the Microsoft ODBC drivers for SQL Server is trying to be installed (e.g. x86\msodbcsql.msi on the Windows 64-bit version), the following warning message will appear:
Let’s install the appropriate Microsoft ODBC drivers for SQL Server. The process of installation of the Microsoft ODBC drivers for SQL Server is very simple and straightforward. Double click on the (e.g. msodbcsql.msi ) file and the welcome screen will appear:
Click the Next button to proceed with installation. Under the License Agreement window read the license terms and if you agree, check the “I accept the terms in the license agreement” radio button and click the Next button:
On the Feature Selection window, select the Client Components item and click the Next button:
Click the Install button on the Ready to Install the Program window to begin the installation of the Microsoft ODBC drivers for SQL Server:
In a few seconds, the installation of the Microsoft ODBC drivers for SQL Server finishes. Click the Finish button to close the Microsoft ODBC drivers for SQL Server Setup window:
To confirm that the Microsoft ODBC drivers for SQL Server is installed, go to Control Panel and under the Program and Features find the Microsoft ODBC drivers for SQL Server:
Or open the ODBC Data Source Administrator dialog box:
Under the Drivers tab, check iif the ODBC Driver for SQL Server driver exists:
The ODBC Data Source Administrator dialog box is used to create and manage ODBC data sources. The ODBC Data Source Administrator dialog box is a Windows component.
To open the ODBC Data Source Administrator dialog box, go to the Start menu under Windows Administrative Tools, choose ODBC Data Sources:
In the Control Panel under Administrative Tools, choose appropriate ODBC Data Sources:
Or simpler, in the Windows search box, type the ODBC Data Sources word:
And from the search list, choose appropriate:
As you may notice, there are two versions of the ODBC Data Sources Administrator dialog boxes: one is the ODBC Data Sources Administrator (32-bit) and another is the ODBC Data Sources Administrator (64-bit) version. Since Windows 8 is the operating system, there are two the ODBC Data Sources Administrator dialog boxes, one is for 32-bit ODBC Data Sources and the other is for 64-bit ODBC Data Sources.
Note: If the ODBC Data Sources Administrator (64-bit) dialog for creating connection with 32-bit data source (e.g.32-bit SQL Server) is used, the following error may appear when a linked server is created:
The linked server has been created but failed a connection test. Do you want to keep the linked server?
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “SERVERX86”.
OLE DB provider “MSDASQL” for linked server “SERVERX86” returned message “[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application”. (Microsoft SQL Server, Error: 7303)
To create a new SQL Server ODBC data source, press the Add button under the User DSN or System DSN tab. Both of these tabs are used to create a connection to ODBC data source, the difference is that the User DSN tab will show data source only for a currently logged user on this computer. While creating data sources under the System DSN, it will be available to any user that is logged on to this computer.
For the purpose of this article, the SQL Server ODBC data source will be created under the System DSN tab. To start creating connection, press the Add button:
This will open the Create New Data Source dialog, from the list, choose a driver for which you want to set up a data source. In our case, that will be ODBC Driver 13 for SQL Server:
After selecting a desired driver, click the Finish button, this will open the Create a New Data Source to SQL Server dialog:
Under the Name box, enter the connection name, the name can be whatever you like (e.g. TestServer). The Description box is optional and it’s for a description of the data source. In the Server field, enter a name of a SQL Server for which you want to connect to (e.g. WIN10\SQLEXPRESS):
If a SQL Server instance is the default instance, type the name of the computer that hosts the instance of SQL Server (e.g. WIN10). If SQL Server is a named instance, type the name of the computer and the name of the instance separated by a slash (e.g. WIN10\SQLEXPRESS). Otherwise, the following message will appear when testing the SQL Server ODBC data source connection and the name of the SQL Server instance is wrong:
After setting the appropriate Server and connection name, press the Next button. In this dialog, how to connect to SQL Server by using Windows authentication can be specified, including Active Directory authentication or using SQL Server authentication.
For the purpose of this article, SQL Server authentication will be used. Check the With SQL Server authentication using a login ID and password entered by the user radio button and, in the Login ID and Password field, enter appropriate credentials and press the Next button:
On this dialog, set the database that you want to connect to, by checking the Change the default database to check the box and from the combo box, choose a deserted database.
During the change of the default database the following message may appear:
This means that communication link between the driver and the data source to which the driver was attempting to connect failed.
To resolve this, close the ODBC Data Sources Administrator dialog box and try again to create connection to ODBC data source.
If that isn’t successful, check if Named Pipes under the Protocols for SQL Server for which you want to create a connection to (e. g. SQLEXPRESS) are enabled:
Don’t forget to restart SQL Server under the SQL Server Services after enabling Named Pipes in order to changes have effect.
Make sure that the SQL Server and Windows Authentication mode radio button under the Security tab of the Server Properties dialog is checked:
Also, check if the Allow remote connections to this server check box under the Connections tab is checked:
After these settings are done, under the Change the default database to combo box, available databases will appear, choose a database (e.g. AdventureWorks2014) and click the Next button:
This will open one more dialog with more options to set. These options will be left as it is and click the Finish button:
After, clicking the Finish button, the ODBC Microsoft SQL Server Setup window will appear with configuration information that we set for the ODBC data source. At the bottom of the ODBC Microsoft SQL Server Setup window, there is the Test Data Source button, by clicking on this button, it can be checked if the connection with the data source is established successfully or not:
In case that connection with data source is established successfully, on the SQL Server ODBC Source Test window, the “TESTS COMPLETED SUCCESSFULLY” message will appear:
To confirm that the ODBC data source connection is created, in the ODBC Data Source Administrator dialog box under the System DSN tab, the name of the ODBC data source that was created will appear:
Deleting an ODBC data source
To delete ODBC data source in the ODBC Data Source Administrator dialog box, select the desired item from User DSN or System DSN tab and click the Remove button:
As you may notice, under the System DSN tab, the LocalServer name is selected, but the Remove button is disabled. This happens because we are trying to delete a 32-bit System DSN from 64-bit ODBC Data Source Administrator dialog box. To delete 32-bit DSN, open the 32-bit ODBC Data Source Administrator dialog box and click the Remove button:
Now, when the ODBC data source is created, let’s create and configure a Linked Server using the ODBC.
Creating and configure a Linked Server using the ODBC driver via SQL Server Management Studio
In SQL Server Management Studio (SSMS) go to the Object Explorer, right click on the Linked Servers folder and, from the context menu, choose the New Linked Server command:
This will open the New Linked Server dialog:
Under the General tab of the New Linked Server dialog in the Linked server text box, enter a name of a new linked server (e.g. ODBC_SERVER). in the Provider combo box, choose the Microsoft OLE DB Provider for ODBC Drivers item. The Product name is an identifier and any appropriate value for this field (e.g. TestSarever) can be used. The data source must match a system data source defined in the ODBC Data Source Administrator dialog box System DSN tab:
Under the Security tab, choose the Be made using this security context radio button, then enter the username and password for a user account existing on the TestServer instance that was chosen as our data source:
For more about the Security and Options tab, see the How to create and configure a linked server in SQL Server Management Studio page.
After, the security configuration is done, press the OK button on the New Linked Server dialog to create a new linked server. A newly created linked server will appear under the Linked Servers folder:
To test if the connection with the ODBC data source is established successfully, right click on the ODBC_SERVER linked server and, from the context menu, choose the Test Connection command:
If a connection with the ODBC data source is established successfully, the following info message box will appear:
In case that the connection with a linked server is created successfully, but under the Catalogs folder of the linked server, only the default database is shown:
To resolve this, close SQL Server Management Studio and run it again, but this time as an administrator:
Another way is to go to the SQL Server Configuration Manager:
From the SQL Server Configuration Manager dialog, select SQL Server for which a linked server has been created:
From the context menu, choose the Properties option:
Under the Log on tab of the SQL Server Properties dialog, choose the Built-in account radio button and from the combo box, select the Local System item:
From the SQL Server Properties dialog, click the Apply button and press the Yes button on the Confirm Account Change warning message box:
Now, open SQL Server Manage Studio as administrator and under the Catalogs folder, the databases should appear: