How can I connect to a local ODBC datasource

Question

I created an ODBC database on my local machine with driver SQL server Native client 10.0, which connects to a remote server, see

.

I am working on a project about customized ODBC to an inhouse database and want to Test how I can connect to a data source using ODBC. The question is how can I connect to the local ODBC using sql server studio manager? I tried

but it returns an error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Any idea?

Answer 1

Connect to your local server through SSMS then create a linked server to the ODBC connection.

Answer 2

Echoing @Brian Boyd...

It is possible to create an ODBC connection to a SQL server running locally.

If you start SSMS and point it to a local database engine the Server Name should be Computer_Name\SQLEXPRESS with Authentication set to Windows Auth (ie the logged-in user: you). Computer_Name is found in the System window (Windows Key + Break) and is not localhost, 127.0.0.1, etc.

So, to set up an ODBC connection by running %windir%\syswow64\odbcad32.exe

• In the System DSN tab, click [Add...]
• Select SQL Server in the drivers list, click [Finish]
• Now add a Name and Description but most importantly set the Server to be Computer_Name\SQLEXPRESS (whatever was shown in SSMS)
• Click [Next] and leave the authentication set to Windows NT
• Click [Next] and tick the default database tickbox to reveal a list of databases locally (if yours is listed here the ODBC settings have already worked)
• Click [Next], [Finish] then [Test Data Source...] then all should be well

Answer 3

To connect to a remote server you don't need a DSN. You can enter the server name in the Server name field of SQL Server Management Studio and select Windows authentication or Database authentication.

It is not possible to connect SSMS to an ODBC data source. The only way is to create a linked server in your local SQL server as @Brian Boyd described.

Answer 4

Instructions are:

https://community.sagecrm.com/partner_community/b/hints_tips_and_tricks/archive/2010/05/10/connecting-to-a-sage-mas-erp-90-database-as-a-linked-server-within-ms-sql-server-2008.aspx

... and sp_AddLinkedServer documentation from MS HERE … and with search = “sp_addlinkedserver for SOTAMAS90“, even an example from 2005 https://blog.coryfoy.com/2005/06/lets-go-crazy-accessing-timberline-pervasive-data-from-a-sql-linked-server/ Let’s see what turn says …. And think I will / would get the same error adding a linked server through UI that I get TSQL

Based on above, I tried …

EXEC sp_addlinkedserver

@server = 'TimberlineTest',

@provider = 'SOTAMAS90',    -- Original command  @provider = MSDASQL',

@srvproduct = '',           --- MAS 90 4.0 ODBC Driver  Original is      @srvproduct='Timberline Data',

@datasrc = 'DataTest'