How to fix sql 17 connection error in SSMA for Access
I am trying to use Sql Server Migration Assistant 7.8 for Access to migrate access tables from Access to SQL Server 17 and then link the tables in Access to the new SQL back end tables. SSMA is able to create the SQL Database, create the tables, and migrate the data successfully using trusted windwos connection. However the Linking step is failing as I am getting a Connection Failed error from SQL... See Error Below:
Research show this may be caused by miss-configuration of TLS 1.2. I have made sure that both my workstation and my server have TLS 1.2 to be enabled for both client and server. Also I did not install Sql Native Client as Microsoft has deprecated it, Instead, I installed Microsoft's un-depricated ODBC for SQL driver MSODBSSQL_17. I can go into the Access database after the data has been migrated and manually create links to tables in the new SQL back end.
Any help getting around this error would be appreciated.
Research show this may be caused by miss-configuration of TLS 1.2. I have made sure that both my workstation and my server have TLS 1.2 to be enabled for both client and server. Also I did not install Sql Native Client as Microsoft has deprecated it, Instead, I installed Microsoft's un-depricated ODBC for SQL driver MSODBSSQL_17. I can go into the Access database after the data has been migrated and manually create links to tables in the new SQL back end.
Any help getting around this error would be appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
John Tsioumpris
Are Named Pipes and TCP/IP protocols enabled at the server ?
Please follow the steps mentioned below and reboot your server to get the issue fixed out..
https://community.ipswitch.com/s/article/How-to-resolve-SSL-Security-error-and-SECCreateCredentials-failures-in-Admin-Console-and-ODBC-connections-after-disabling-SSL-and-TLS1-0-protocols-on-the-WhatsUp-Gold-server
https://community.ipswitch.com/s/article/How-to-resolve-SSL-Security-error-and-SECCreateCredentials-failures-in-Admin-Console-and-ODBC-connections-after-disabling-SSL-and-TLS1-0-protocols-on-the-WhatsUp-Gold-server
ASKER
PAT: Although more of a workaround than a solution to the problem, it is a very viable workaround made more appealing by the fact you provide code to automate the renaming of the manually linked tables. If we do not come up with an actual solution, I will mark your answer as the correct one. Thank you for providing the code.
JOHN: Yes, Named Pipes and TCP/IP protocols are enabled on the server. I can connect to my SQL Server remotely using SSMS using both Windows Authentication, as well as SQL Server Authentication. As mentioned above, I can also connect to the SQL Server via MS Access using a DSN to manually link tables
RAJA: I had viewed the article you link to several times while researching the problem on my own. I did not want to give it a try as I was under the opinion that you did not want to enable FIPS unless you were actually working with the Federal Government, However since you mentioned the article, I was hopeful that you had used this approach and it had worked for you, So I enable FIPS (and yes I rebooted the server), but still received the same error, so I disabled it again.
TO ALL: I find it strange that SSMA for Access can connect to the SQL Server so that it can create the database, the tables, and to migrate the data from the Access database, but it fails to connect when trying to link the tables in Access. This makes me wonder if the problem is not with SSMA, but with Access not being able to connect to SQL Server, and that SSMA is just relaying the error message.
Any other ideas floating out there?
JOHN: Yes, Named Pipes and TCP/IP protocols are enabled on the server. I can connect to my SQL Server remotely using SSMS using both Windows Authentication, as well as SQL Server Authentication. As mentioned above, I can also connect to the SQL Server via MS Access using a DSN to manually link tables
RAJA: I had viewed the article you link to several times while researching the problem on my own. I did not want to give it a try as I was under the opinion that you did not want to enable FIPS unless you were actually working with the Federal Government, However since you mentioned the article, I was hopeful that you had used this approach and it had worked for you, So I enable FIPS (and yes I rebooted the server), but still received the same error, so I disabled it again.
TO ALL: I find it strange that SSMA for Access can connect to the SQL Server so that it can create the database, the tables, and to migrate the data from the Access database, but it fails to connect when trying to link the tables in Access. This makes me wonder if the problem is not with SSMA, but with Access not being able to connect to SQL Server, and that SSMA is just relaying the error message.
Any other ideas floating out there?
CALAOMS,
I don't like work arounds and if this were an integral part of your application I would invest the time to figure out the problem. However, once you convert the database, you are free of the problem and hopefully the next time you need SSMA it will be back in working order. You are ahead of the game if SSMA actually creates the database correctly (it doesn't work at all for me). I ended up writing code to create DDL to recreate the Access tables and relationships in SQL Server. MS is breaking things right and left. a month ago the Eval() statement simply stopped working. Apparently enough people complained so that they fixed the problem sooner rather than later and I found that last week the broken app was working again. All I can say is that more people need to complain. At the moment, in my version of Access, I have a feedback option on the back stage menu and I've been using it.
I don't like work arounds and if this were an integral part of your application I would invest the time to figure out the problem. However, once you convert the database, you are free of the problem and hopefully the next time you need SSMA it will be back in working order. You are ahead of the game if SSMA actually creates the database correctly (it doesn't work at all for me). I ended up writing code to create DDL to recreate the Access tables and relationships in SQL Server. MS is breaking things right and left. a month ago the Eval() statement simply stopped working. Apparently enough people complained so that they fixed the problem sooner rather than later and I found that last week the broken app was working again. All I can say is that more people need to complain. At the moment, in my version of Access, I have a feedback option on the back stage menu and I've been using it.
>> I find it strange that SSMA for Access can connect to the SQL Server so that it can create the database, the tables, and to migrate the data from the Access database, but it fails to connect when trying to link the tables in Access
Okay, missed noticing this part while responding earlier and suggested the FIPS option..
Since you are having issues when linking tables, kindly let us know whether you have any huge tables available in your database.
If so, then try increasing your ODBC timeout if possible..
Okay, missed noticing this part while responding earlier and suggested the FIPS option..
Since you are having issues when linking tables, kindly let us know whether you have any huge tables available in your database.
If so, then try increasing your ODBC timeout if possible..
ASKER
RAJA: Since SSMA takes time to analyze the Access database, I am just trying to get SSMA to work on a test database with two tables of 2000 records or less and one query. However, giving it a try anyway, increasing ODBC timeouts did not help.
PAT: I agree about workarounds, I always try to find a solution, but do use workarounds when all else fails. Also don't get me started regarding the Love/Hate relationship I have with Microsoft. I used to use SSMA years ago and it worked flawlessly.
Side note: The version of Access that I am running is the Office 365 Click to run version of Office 2016. I am wondering if this could also be a contributor to the problem.
PAT: I agree about workarounds, I always try to find a solution, but do use workarounds when all else fails. Also don't get me started regarding the Love/Hate relationship I have with Microsoft. I used to use SSMA years ago and it worked flawlessly.
Side note: The version of Access that I am running is the Office 365 Click to run version of Office 2016. I am wondering if this could also be a contributor to the problem.
It's hard to say. I have had problems with tools from FMSINC when I was using O365 but I no longer have any "box" versions of Office installed anywhere. I am not a fan of the software as a service model and being on the bleeding edge of the distribution cycle makes me feel like I'm surrounded by picadors (the guys that stick stuff in the bull to weaken him for the matador)..
Maybe, by the time you actually need to do the conversion, SSMA will be working again.
Maybe, by the time you actually need to do the conversion, SSMA will be working again.
>> if this could also be a contributor to the problem
Definitely could be contributing to the problem.
I would be trying Pat's first post.
You mention the un-deprecated ODBC driver, I thought OLEDB was the focus of the un-deprecated release....
Meaning ODBC and SQLNCLI are pretty much unchanged from the SQL Server 2016 install (Well, SQL Server 2016 (13.x)) Would have expected the same from 2017 install.
There is the separate ODBC download / install (https://www.microsoft.com/en-us/download/details.aspx?id=56567 ) but you have to first install (according the requirements) : https://www.microsoft.com/en-us/download/details.aspx?id=28177
Back on OLEDB... Have you read through : https://blogs.msdn.microsoft.com/sqlnativeclient/2018/03/30/released-microsoft-ole-db-driver-for-sql-server/ noticing the changes to the connection string ?
Definitely could be contributing to the problem.
I would be trying Pat's first post.
You mention the un-deprecated ODBC driver, I thought OLEDB was the focus of the un-deprecated release....
Meaning ODBC and SQLNCLI are pretty much unchanged from the SQL Server 2016 install (Well, SQL Server 2016 (13.x)) Would have expected the same from 2017 install.
There is the separate ODBC download / install (https://www.microsoft.com/en-us/download/details.aspx?id=56567 ) but you have to first install (according the requirements) : https://www.microsoft.com/en-us/download/details.aspx?id=28177
Back on OLEDB... Have you read through : https://blogs.msdn.microsoft.com/sqlnativeclient/2018/03/30/released-microsoft-ole-db-driver-for-sql-server/ noticing the changes to the connection string ?
ASKER
Mark,
You are correct, ODBC was not deprecated but OLE DB was deprecated. I did confuse ODBC with OLE DB when referencing that it was un-deprecated. That's what happens when you pour through too many online docs and don't pour yourself enough coffee!
As far as installing "Microsoft Online Services Sign-In Assistant for IT Professionals RTW" as a requirement for the ODBCSQL17 driver, my understanding is that it only needs to be installed if you plan to use "Azure AD Authentication", which I am not. I am currently using Windows Authentication on a SQL Server
Also I should note that I did eventually install SQL Native Client 11, as it is a requirement of SSMA, but that had no effect on the issue of connecting to the SQL Server when attempting to link tables... it yielded the same results as just having the ODBCSQL17 driver installed.
You are correct, ODBC was not deprecated but OLE DB was deprecated. I did confuse ODBC with OLE DB when referencing that it was un-deprecated. That's what happens when you pour through too many online docs and don't pour yourself enough coffee!
As far as installing "Microsoft Online Services Sign-In Assistant for IT Professionals RTW" as a requirement for the ODBCSQL17 driver, my understanding is that it only needs to be installed if you plan to use "Azure AD Authentication", which I am not. I am currently using Windows Authentication on a SQL Server
Also I should note that I did eventually install SQL Native Client 11, as it is a requirement of SSMA, but that had no effect on the issue of connecting to the SQL Server when attempting to link tables... it yielded the same results as just having the ODBCSQL17 driver installed.
ASKER
Although Pat's answer does not fix the original problem, it is the best solution that allows me to move forward with the project and requires a minimal amount of effort on my part.
All I do is manually link the SQL tables created by SSMA in Access and then run the code that Pat graciously provides. The code renames the linked table(s) by removing the preceding dbo, and asks if I want to replace the existing (original) table(s) of the same name. This essentially completes the task of linking tables in Access that SSMA fails at.
Thanks Pat, I appreciate the code!
All I do is manually link the SQL tables created by SSMA in Access and then run the code that Pat graciously provides. The code renames the linked table(s) by removing the preceding dbo, and asks if I want to replace the existing (original) table(s) of the same name. This essentially completes the task of linking tables in Access that SSMA fails at.
Thanks Pat, I appreciate the code!
You're welcome. Sometimes it's just not worth the effort to fix one of these off the wall problems. Products like SSMA have such a small user base that they are not tested as extensively as the main products are and it's not like we upsize an application every day so a rube goldberg solution gets us over the hump. Glad I had the code because renaming the tables is a PITA. MS should never have removed the upsizing wizard from Access. It always worked and SSMA has been nothing but trouble for me. As far as I am concerned, SSMA is not "better" than the built in wizard if it just doesn't work.