Sage 100

List, We have a client that is having a very strange issue with ODBC drivers when we try to setup a linked server in SQL. No matter what we try we get an error that seems to suggest that the login is failing but ONLY when we try to access it from SQL. Using ODBC Configuration and excel we can use the ODBC drivers just fine. (text of error is at the end of this email) Some important things: Prophix is trying to do an integration with Sage100 and they called us in to assist with the ODBC and SQL Link Sage100 is using unified logins, we setup an ODBC driver (64 bit) The ODBC tests correctly in ODBC management using Unified, or Direct Sage100 Username/Password login. The ODBC also works in Microsoft Excel and we can pull data into a spreadsheet without any problems. The Error occurs when trying to add linked server to SQL. We Switched SQL service to Local System, and a specific account with Sage Access, no change in result. Sage and SQL are installed on different machines in the Same domain. Gave full rights to Sage directory to all users, no change in result. Tried setting up ODBC in SQL using both Unified, and Direct Sage100 Username/Password login, no success. System was previously upgraded from MAS90 4.5 to 2016, but this is something new we are trying so I have no idea if it would have worked prior to the upgrade or not. HERE IS THE ERROR: MICROSOFT SQL SERVER MANAGEMENT STUDIO 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. (Microsof.SqlServer.ConnectionInfo) Cannot initialize the data source object of the OLE DB provider ""MSDASQL"" for the linked server ""SAGE64SMS"" OLE DB provider ""MSDASQL"" FOR LINKED SERVER ""SAGE64SMS"" Returned message ""[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed"". OLE DB provider ""MSDASQL"" for linked server ""SAGE64SMS"" returned message ""[Providex]ODBC Driver][PVKIO]Logon failed"". (Microsoft SQL Server, Error:7303)

The SQL Server is the 64 bit version? Prior to SQL Server 2016, SQL Server could be either 32 to 64 bit.

if SQL is 64-bit then you'd need to install the Sage 64 bit ODBC

we did install the 64 bit ODBC driver. I'm assuming that SQL is a 64 bit version but I'm checking on that now

Confirmed: the SQL is the 64 bit version

Is the ODBC connection the linked server is using 64-bit. There are two different odbcad32.exe programs.

For linked servers I always use SQL authentication, not integrated. That forces the security context to be from the SQL Service, which I set to run with a domain login with proper permissions to access MAS90.

(I've never done this with an integrated Sage 100 system though).

I have also found that completely deleting the ODBC System DSN's AND the existing linked servers works. Make sure to test the System DSN's on the Debug tab to see that you are accessing the tables.

Use a UNC in the connection string (not a mapped drive), and there are some SQL commands to run as well. I've attached my notes on setting these up:

Thanks - lots of great suggestions. - I'm sending all of them to our SQL Guru I'll keep you posted and keep the ideas coming if you have more!

we have seen this a lot.. check to make sure the account that is running the SQL instance is a domain admin account. also make sure that the share were Sage is for ODBC connection is shared with the right permissions and propagate to child objects. also make sure that in the SQL Configuration Manager that TCPIP for that instance is turned on. I have more but try this....

I agree with @JoeBisaha. You need to ensure that the SQL service is running using a Domain account, not local service (as stated in your original post). The local service account does not have access rights to resources on other servers on the domain, only to local resources (thus local service.) Also ensure that the domain account you use to run the SQL service has access rights to the Sage 100 directories on the Sage 100 server. People not familiar with SQL tend to forget that when you set SQL up to do something (ie, create a linked server and have SQL connect to a remote resource), the account used by the SQL server to perform these tasks is the account the SQL service is running under.

I agree with @KevinMoyes. You are probably running into SQL Server Double-Hop issue. If Sage is not loaded on same server as SQL, accessing it from a workstation requires Kerberos security setup when using Windows Authentication. Use SQL authentication for linked server, or move SQL Server instance to same server as Sage.

OH Ya and if they are separate servers then we have found that you can load the C/S service and check that Option in ODBC (with a registry hack) and it will make it way faster than standard ODBC. if you want to see this David let me know we can show you how we set this up. common setup for our hosted Scanning system.

just an update. the SQL Service is running as local admin and we are waiting on IT to give us a username and pw with domain admin.

OK, I'm not sure if this is progress or not. we finally got a domain admin user name and PW from the IT staff. We are running SQL service and SQL Agent under that login. now when we try to save the link we get the notorious spinning circle and ""executing"" - it has been running for about 10 minutes now I don't know if I mentioned the version of SQL - it is Microsoft SQL Server 2014 (SP2-GDR) (KB3194714) - 12.0.5203.0 (X64) Sep 23 2016 18:13:56

Be sure to stop/restart the Sql Server service and the Browser service

you can also open up your SQL monitor and find the SPID that is stuck and kill it. if it happens again you can track a possible issue if the same Spid keeps getting stuck.

we did stop and restart the service. The link creation is what is hanging up - would checking the SPID still apply?

When you connected to the server using SSMS, did you use SQL authentication?

yes we did use SLQ authentication and tried using Windows authentication same results on both

(Always use SQL authentication when working with Linked Servers). Did you scrutinize the connection string being used for the Linked Server to ensure there are no typos? I've had something like a comma instead of a semi-colon cause many many problems...

I'll have them check that. the only reason we tried using windows authentication was that the installation used Unified logon and we thought perhaps that was relevant. I think this is the only client I have using unified login setting in Sage100.

Make sure the SQL Service Windows login has Sage 100 permissions, then using a SQL login when working in SSMS will force that service ID to be used for the Sage connection.

Thanks Kevin, That was it, we had tried that before and it did not work; after giving that user Domain Admin rights it started working. apparently the two things below are what we needed to do: SQL service has to be running with a login that has DOMAIN admin rights (it cannot be run as local admin) Domain admin account also has to be a valid sage user since Unified Login is turned on in Sage100