Cannot connect to SQL database from SQL Migration Assistant
I am trying to use the SQL Migration Assistant to upgrade an Access database to SQL. I cannot get the Migration Assistant to connect to the SQL. I get this error no matter what user password combo I try: 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)
I am using SQL Express and when I use SQL Management Studio I can log in with no problem.
This is not my forte at all so I know I am missing something simple.
I am using SQL Express and when I use SQL Management Studio I can log in with no problem.
This is not my forte at all so I know I am missing something simple.
As suggested, double check SQL server surface configuration for the installed server instance that TCP/ip is enabled and then that the Windows firewall allows the connection in on the incoming side of the advanced firewall settings.
Make sure to note that the incoming rule applies to the network categorization ....
Make sure to note that the incoming rule applies to the network categorization ....
ASKER
Hello All
Thanks for the reply, I checked and TCP/IP is enabled and I completely shut off the firewall to see if that was it, and no dice. Anything else I could try? Thanks
Thanks for the reply, I checked and TCP/IP is enabled and I completely shut off the firewall to see if that was it, and no dice. Anything else I could try? Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Alevin
I disagree with that solution. The reason I disagree is that the example has SQL installed as a named instance, and it's likely you had the same. By forcing it to use port 1433 you have stopped the SQL Browser service using that port and doing the instance name resolution.
If instead of trying to connect to yourservername you used yourservername\SQLEXPRESS.
Now, SSMS will have figured that out and automatically attempted to connect to that named instance. But The Agent is a bit more picky.
Regards
David
I disagree with that solution. The reason I disagree is that the example has SQL installed as a named instance, and it's likely you had the same. By forcing it to use port 1433 you have stopped the SQL Browser service using that port and doing the instance name resolution.
If instead of trying to connect to yourservername you used yourservername\SQLEXPRESS.
Now, SSMS will have figured that out and automatically attempted to connect to that named instance. But The Agent is a bit more picky.
Regards
David
Presumed the TCP/ip option was not set or the firewall not enable. Have not used the migration assistant so not sure it is capable on searching the dynamic port or whether SQL browser was running.
The asker resolved the issue that worked for them.
The asker resolved the issue that worked for them.
Hi Arnold,
My point is that there may be other possible solutions which have better longer-term outcomes. And imho it's worthwhile making that note against this solution for the record so to speak.
Regards
David
My point is that there may be other possible solutions which have better longer-term outcomes. And imho it's worthwhile making that note against this solution for the record so to speak.
Regards
David
David, agree to a point.
I think a comment in the alternative, or additional way to solve this issue is
Would potentially bee seen as additional insight as an approach to a similar situation.
While felt the same on choices askers used to determine their solution, the Asker resolve the issue based on the migration assistant which by the looks of the question does not have dynamic port, named pipes capabilities.
While it is true, that using ssms the asker would have successfully connected, to what end? The excersise would confirm the SQL login used is true, but the tool used, migration assistant from access will still return the same error, that it can not connect.
The flow of the question refreshes that simple questions are over time overlooked and subsequent questions on this topic where I participate, I will ask about the port for a time..... And then at some point will run into a question where the port is not explicitly set.
I think a comment in the alternative, or additional way to solve this issue is
Would potentially bee seen as additional insight as an approach to a similar situation.
While felt the same on choices askers used to determine their solution, the Asker resolve the issue based on the migration assistant which by the looks of the question does not have dynamic port, named pipes capabilities.
While it is true, that using ssms the asker would have successfully connected, to what end? The excersise would confirm the SQL login used is true, but the tool used, migration assistant from access will still return the same error, that it can not connect.
The flow of the question refreshes that simple questions are over time overlooked and subsequent questions on this topic where I participate, I will ask about the port for a time..... And then at some point will run into a question where the port is not explicitly set.
Download SQL Server Management Studio and try to connect to SQL server.
Does this work?
If not you have to examine the configuration of SQL starting by the simplest...enable remote connection (Protocols -->TCP/IP)