none
Using SQL Server Migration Assist for Access - Unable to connect to SQLserver express

    Question

  • Good day, I am trying to use the SQL Server Migration Assistant for Access to try and migrate a Access 2010 database to SQL Server Express.

    I registered and got the key for the migration assistant, but can not figure out exactly how to connect to my instance of SQL Server (running in a separate Windows 7 machine). When I enter the name of my server (using the same format as I am using when creating a DNS), nothing happens. I am expecting the migration tool to show that it is connected to the server and populate a list of the databases on the SQL server, but I do not see this.

    Any help would be appreciated.

    Thanks

     

    Friday, January 13, 2012 4:13 PM

All replies

  •  The account which you are using to connect to sql server does it have enough permissions? any errors in windows event / SQL errors logs?

    http://uk.linkedin.com/in/ramjaddu
    Friday, January 13, 2012 4:50 PM
  • Hi Andre,

    Make sure that you can connect to your instance of SQL Server before launching SSMA.

    Please see this blog: Access to SQL Server Migration: How to Use SSMA: http://blogs.msdn.com/b/ssma/archive/2011/01/28/access-to-sql-server-migration-how-to-use-ssma.aspx.


    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    Tuesday, January 17, 2012 5:24 AM
    Moderator
  •  The account which you are using to connect to sql server does it have enough permissions? any errors in windows event / SQL errors logs?

    http://uk.linkedin.com/in/ramjaddu
    Yes, it does, set as DBO
    Tuesday, January 17, 2012 5:40 PM
  • Hi Andre,

    Make sure that you can connect to your instance of SQL Server before launching SSMA.

    Please see this blog: Access to SQL Server Migration: How to Use SSMA: http://blogs.msdn.com/b/ssma/archive/2011/01/28/access-to-sql-server-migration-how-to-use-ssma.aspx.


    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.


    Maggie,I have full DBO level access to the database on SQL server.

    In fact, I have a split access database where the backend is on SQL server, so that should confirm that I have proper rights.

    The migration assistant just seems like it is not checking for the server when I added the server IP address (server is on another machine), I expected a slight hisitation and maybe an hourglass indicating that it is trying to find the server.

    I read somewhere that I should pipes to connect to the server instead of server name / ip address. Can you perhaps help me with the proper syntax?

    Say may server name is LAXSQL, on IP address 10.7.7.7 (not real values)

    What should the pipe read like \\10.7.7.7\sql\query ?

    Usually, if connecting from an external source or with a DSN I just specify the IP address.

     

    Thanks

     

    PS thanks for the link, looks like a great resource.

     

    Tuesday, January 17, 2012 5:48 PM
  • Andre,

    I would expect the database dropdown to be populated as well. I ran into the same situation as you and just manually entered the database name in and it worked for me. I don't know why they have a dropdown for databases as I don't think the program actually attempts to discover databases on the SQL Server entered. It would have been more apparent if the database entry was a textbox instead of a dropdown.

     

    Mark

    Sunday, February 05, 2012 4:06 PM
  • Hi Andre,

    In the SSMA UI when you are promted to enter the SQL Server information please enter the IP or Hostname of the machine where SQL resides followed by the port number (This would be different from 1433 and you can find it in the SQL Error logs) of the SQL Express instance. For example LAXSQL,52678 or 10.7.7.7,52678. Also check if the SQL Server Express instance is enabled to listen on TCP and the firewall on the SQL Server machine allow incoming traffic on the port SQL is listening on e.g  52678 for the above case.

    It would also help if you proivide the exact error that you get when the connection fails.

    Thanks,

    Angshuman Nayak (MSFT)

    Tuesday, February 07, 2012 4:46 PM
  • Follow the instruction here:

    http://support.microsoft.com/kb/914277

    The instructions should also work for SQL Server 2008 Express

    Thursday, February 09, 2012 3:56 AM
    Moderator
  • hi please chech the below link.

    http://www.bigresource.com/MS_SQL-SSMA-with-SQL-Server-Express-oEJXQt7e.html

    Tuesday, May 01, 2012 10:05 AM