MicheleRobinson
asked on
Does the SQL Server Migration Assistant (for MS Access) work with 2008
Currently trying to migrate an Access 2000 application to SQL Server 2008. The connection seems to fail saying that
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that underthe default settings SQL Server does not allow remote connections. [provider: Named Pipes Provider, error:40 - Could not open a connection to SQL Server]"
I can open the database using the SQL Server Management Studio. I have Googled this and have opened up TCP IP, Named Pipes, etc. I also have checked the Allow remote connections to this server under the Connections page. I have stopped and restarted the Sql Server. I have enabled the SQL Server Browser service. I also have rebooted the PC Server. No matter what I do, I seem to have the same issue.
Wnen running the Migration Assistant software, it does not show any instance of SQL Server. In the instance window, it only shows what I have typed in for a server name. Any other tips?
Thanks. FYI, this is SQL Server 2008, Running on Windows Server 2003.
Regards,
-Vernon
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that underthe default settings SQL Server does not allow remote connections. [provider: Named Pipes Provider, error:40 - Could not open a connection to SQL Server]"
I can open the database using the SQL Server Management Studio. I have Googled this and have opened up TCP IP, Named Pipes, etc. I also have checked the Allow remote connections to this server under the Connections page. I have stopped and restarted the Sql Server. I have enabled the SQL Server Browser service. I also have rebooted the PC Server. No matter what I do, I seem to have the same issue.
Wnen running the Migration Assistant software, it does not show any instance of SQL Server. In the instance window, it only shows what I have typed in for a server name. Any other tips?
Thanks. FYI, this is SQL Server 2008, Running on Windows Server 2003.
Regards,
-Vernon
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
are you sure you have set up it corretly?
also some time firewalls maybe turn off but they work i had a firewall uninstalled and it was working...(comodo)
also some time firewalls maybe turn off but they work i had a firewall uninstalled and it was working...(comodo)
ASKER
Miltiadis23,
I am not sure that I have it set up correctly since I don't know what I don't know. But I do know that MS Access ODBC driver sees the SQL Server and can make a connection. However, the Migration Tool does not. Why? I have no idea.
What I will do today is to install the SQL Server 2005 trial version and see if it works there. I am beginning to get a little discouraged with the tool. I suppose that I could migrate the data manually using the 32bit transfer tool. It seems that the Migration Assistant is not so good at handling queries anyway.
Thanks,
-Vernon
I am not sure that I have it set up correctly since I don't know what I don't know. But I do know that MS Access ODBC driver sees the SQL Server and can make a connection. However, the Migration Tool does not. Why? I have no idea.
What I will do today is to install the SQL Server 2005 trial version and see if it works there. I am beginning to get a little discouraged with the tool. I suppose that I could migrate the data manually using the 32bit transfer tool. It seems that the Migration Assistant is not so good at handling queries anyway.
Thanks,
-Vernon
ASKER
I installed Sql Server 2005 edition and it does exactly the same thing. It must be a firewall or something. I installed Sql Server 2005 edition on another laptop and the Access Migration Tool works fine. I am running McAfee Total Protection for Small Business on the Server and McAfee Security Center on the desktop pcs.
I went to the McAfee Website to try and set permissions for McAfee for Small Business. I thought I understood how to do it, but it is still behaving the same way.
Looking for additional ideas. I used the Data Import Tool in Sql Server to solve my immediate need, but I still would like to understand why I can not seem to get the Access Migration tool to see and connect to either 2005 or 2008 if possible.
Thanks again and sorry for the long delay.
Regards,
-Vernon
I went to the McAfee Website to try and set permissions for McAfee for Small Business. I thought I understood how to do it, but it is still behaving the same way.
Looking for additional ideas. I used the Data Import Tool in Sql Server to solve my immediate need, but I still would like to understand why I can not seem to get the Access Migration tool to see and connect to either 2005 or 2008 if possible.
Thanks again and sorry for the long delay.
Regards,
-Vernon
ASKER
Thanks for ttying to help. I'll just close this one out now.
ASKER
I am not quite sure what you are recommending. First, let me explain that I am a developer and not a hardware guy. However, I am trying to get the following setup to run.
1. Workstation is running Windows XP. Not part of a domain, just a local WorkGroup.
2. The Windows 2003 Server where SQL Server is running is also part of the WorkGroup.
3. The Windows workstation is running McAfee for Anti-Virus and Firewall.
4. The Windows 2003 Server is protected by McAfee Total Protection for Small Business. Both the Windows Firewall and McAfee Firewall were turned off.
While trying to run the Migration Assistant on the Server, it did not see either the 2000 SQL Server or the 2008 SQL Server.
Using ODBC from the WorkStation I was able to build an ODBC connection that sees the 2000 and 2008 Server. However, the Migration Tool does not see any of the SQL Servers.
Any other ideas or further explanations?
Thanks,
-Vernon