Cannot complete data connection from Excel to a remote SQL Server
Hello Experts,
One of our users is unable to make a connection from her locally installed copy of Excel to a remote SQL server. We have a number of users who do this on a regular basis, and have spreadsheets with stored and configured data connections. When she attempts to refresh the data through the connections stored in one of these spreadsheets she receives a message that includes "provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.". I then attempted to make a connection from her system to the SQL server from a blank Excel file, and when it gets to the point where you would typically enter the credentials for the connection, the application hangs with no option to enter the credentials. In searching on the error message I found a number of articles referencing the need to obtain a certificate from the SQL server and install it on her system. However, I was able to successfully make the connection through the spreadsheet mentioned above on another system without any need to install a certificate from the remote SQL server. I have reviewed the settings in the Excel Trust Center on her system and to my knowledge they are configured correctly. I'm currently in the process of making sure any and all available updates are installed on her system, but so far that has not changed the result. Any ideas on how to successfully make the data connections from her system?
Thanks in advance,
Russ
One of our users is unable to make a connection from her locally installed copy of Excel to a remote SQL server. We have a number of users who do this on a regular basis, and have spreadsheets with stored and configured data connections. When she attempts to refresh the data through the connections stored in one of these spreadsheets she receives a message that includes "provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.". I then attempted to make a connection from her system to the SQL server from a blank Excel file, and when it gets to the point where you would typically enter the credentials for the connection, the application hangs with no option to enter the credentials. In searching on the error message I found a number of articles referencing the need to obtain a certificate from the SQL server and install it on her system. However, I was able to successfully make the connection through the spreadsheet mentioned above on another system without any need to install a certificate from the remote SQL server. I have reviewed the settings in the Excel Trust Center on her system and to my knowledge they are configured correctly. I'm currently in the process of making sure any and all available updates are installed on her system, but so far that has not changed the result. Any ideas on how to successfully make the data connections from her system?
Thanks in advance,
Russ
Outdated credentials, wrong date/time, different domain or FQDN, using ip instead of server name, many things can get wrong when using a certificate, so as Icohan pointed out, make sure both systems are using the same properties/options/connect
ASKER
Thank you both for your help! I have not yet tried connecting on the second computer using her logon credentials, I will give that a try and will update this post once I have a chance to do that later today. Thanks again!
ASKER
Hello, I apologize for the delay in getting back. Yesterday I had our user log on to the second computer and open the spreadsheet in question, and we were able to complete the data connections and update the sheet without any problems. I then went through the security settings in the Excel Trust Center on both systems and the settings were the same. At that point I thought there might be a problem with the Office install itself. I had already run an online repair, so I downloaded the Office removal tool from Microsoft, ran a complete uninstall of Office on her system and then reinstalled it this morning. After doing that I am still getting the same error. At this point I am beginning to consider a wipe and clean install of the OS and then a clean install of Office. While working on her system I found that it had been in use by a previous user, I don't know if that has anything to do with the current issue or not, but I deleted the old user's profile in an attempt to see if that would help, and it did not. I did note that after uninstalling Office and then reinstalling, when we opened Outlook she was not prompted for a password which made me think that Office was not completely removed. I have found a procedure to completely remove Office manually, and may give that a try. If there is an easier fix to this I would like to find it but at this point it is becoming a question of the time involved to hunt down a needle in a haystack vs the time it will take to revert back to a clean system. If you guys have any other thoughts on what might be causing this in Excel, or how to reset the credentials for Excel data connections, please let me know. Otherwise I anticipate moving forward with the uninstall \ reinstall steps. Thanks again for your willingness to take a look and help out!
if you are part of a domain then maybe a machine policy, so no matter who is logged in it will still happen, also make sure that your firewall settings are correct, if you have one configuration for domain and another for private networks then make sure your network is recognized properly as domain or private or whatever it should be (compared to the other PC)
ASKER
Hello everyone,
Just following up. I ended up putting a blank drive in the system, reinstalling Windows, then Office. After, she was able to immediately and easily complete the data connections in the spreadsheet. I know this doesn't solve the problem of what was actually going on, but it was getting to the point where I was spending much more time trying to work that out than it took to stand up a fresh system for her. Additionally, the fact that the system was behaving in an odd manner when I previously attempted to view and modify the properties of the data connections made me think that a fresh system install was going to be inevitable, eventually prompted me to pursue that course of action anyway. Again, thanks for taking a look and your suggestions.
Russ
Just following up. I ended up putting a blank drive in the system, reinstalling Windows, then Office. After, she was able to immediately and easily complete the data connections in the spreadsheet. I know this doesn't solve the problem of what was actually going on, but it was getting to the point where I was spending much more time trying to work that out than it took to stand up a fresh system for her. Additionally, the fact that the system was behaving in an odd manner when I previously attempted to view and modify the properties of the data connections made me think that a fresh system install was going to be inevitable, eventually prompted me to pursue that course of action anyway. Again, thanks for taking a look and your suggestions.
Russ
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Please try to find out what what is the difference between the two systems in terms of networking/security as this is the main reason that can prevent connection from one to work aside the obvious AD login on one computer that may not have access to the SQL Database/table.
Can you try telnet from the system that doesn't work to the SQL IP:port to see if is working?