Why am I getting a failed connection error from SQL Server Migration Assistant 2008 for Access to SQL Server 2008 Express?

I am a newbie with SQL and I can not connect to SQL Server 2008 Express using SSMA (SQL Server Migration Assistant 2008 for Access).  Both are new installations, the file I am trying to migrate is Access 2003 format.  I am using a Vista Business 64-bit machine, Firewall is off.

When I try to make the connection, I receive the following error:

"Connection to SQL Server failed.
A network-related or instance-specific error occurred while establisihing 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 can open SQL Server Confiiguration Manager and SQL Server Management Studio; they appear to be fine.

I have tried various connection configurations using the "Log On" tab on the properties window for "SQL Server" services.  (SQL Server Surface Area Configuration is not available on SQL Server 2008)

And I have looked on Experts Exchange, including: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23525725.html

Thanks,
Access57Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jmoss111Commented:
The problem is that Remote connections are turned off by default; you're going to have to turn Remote Connections ON. Surface Area Configuration Manager was on the install of SQL 2008 Express that I loaded.

In the Start Menu, open Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Surface Area Configuration
In the Surface Area Configuration utility, click the link "SQL Server Configuration Manager"
Expand "SQL Server Network Configuration" and select Protocols.
Enable TCP/IP. If you need Named Pipes, then you can enable them here as well.
Close the SQL Server Configuration Manager.

Jim




Jim
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Access57Author Commented:
Well, I just solved my on problem -

When the "Connect to SQL Server" pop-up opens, I was manually typing in the SQL instance name (which I am quite sure I was typing in correctly).  I always got the error message that it could not connect.

So I just tried to open SQL Server 2008 again and when the connection screen came on, I just cut and pasted the instance name from the SQL Server 2008 Express box to the SQL Server Migration Assistant box and it worked!

Appartently I did not enter the instance name correctly - but I still do not know why.
0
Access57Author Commented:
Jim,

Thanks for the response.

Per my comment, it is working now.

I installed the SQL Server 2008 Advanced Services download, but I do not have a SQL Server Surface Area Configuration Utility.  I am surprised that you have one, because I checked into it and I thought that I find a note on the Microsoft website that it is not part of 2008.  I do have an insatllation of SQL 2005 and that directory does have it, but it seems to be for SQL 2005.

I have been using Access for a very long time and SQL is much, much more complicated!

Thanks Again
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jmoss111Commented:
I have seen instruction on a M$ site for using Surface Area Manager for 2008, and I did install 2008 on a fresh machine and used the SAM. Did this machine ever have 2005 Express before 2008?

SQL is more complicated but much more robust and blazing fast don't you think? Anyway, Migration is a very good tool.

Have a great day,

Thanks

Jim
0
Access57Author Commented:
Yes, you are correct on both counts.

I found the page on MSDN, and it actually says "Management Studio is not part of the installation (I was looking at a search page description and misinterpreted it)

And yes, I did have SQL Server 2005 Express installed originally on my machine.  I should have uninstalled it, but there were so many different SQL items in the Add Remove program screen I just decided to leave it there (Actually, my first attempt at 2008 was to upgrade 2005; but that did not work, so I just tried again but did a new install of 2008).

I am just starting with SQL and everyone says it is great - I just have a big learning curve ahead of me.

But it is fantastic to have support like you to help me along the way!  Thanks Again!
0
jmoss111Commented:
Management Studio isn't part of the installation but SAM is not part of Management Studio. Management Studio is part of  the Advanced Services download just not the base download. The 05 installation is probably where you picked up the Remote connections On.

And I didn't answer your question, and have no objection if you accept your own answer. You can request attention and have the answer reversed.

The learning curve really is not that difficult and you will be well rewarded by SQL Server by learning.

Have a great day,

Jim
0
Access57Author Commented:
We are good - you had a good comment, and I learned several things through the process, so it was beneficial to me and worth the points.

Thanks for this one.  I am working on a big project and it is time to learn SQL; so I will probably have some more questions down the road.  Maybe our paths will cross again, in the meantime,

thank you and take care.
0
jmoss111Commented:
I hope so Access57. Have a nice evening,

Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.