SQL Server Connection problems

Posted on 2005-05-03
Last Modified: 2008-02-26
First, thanks for taking time to help with this.


- Relatively new Dell 2850 Server - RAID 5
- MS Small Business Server 2003 Premium (has SQL Server Standard with it)
- Accessing server remotely (behind firewall) using MS Remote Desktop mmc
- No SQL server database has been created - we're just starting out getting it setup


I have had problems connecting locally (while 'remoting') to the default database.  At one point I was able to successfully connect and stay connected.  I then went into the properties of the default database from the Enterprise Manager and tried changing the authentication mode from Windows Only to Mixed Mode.  After I made the change, I clicked OK and got the following error after a few seconds:

A connection could not be established to (Local)
Reason: Invalid Connection.
ConnectionOpen(Invalid Instance())..
Please verify SQL Server is running and check your SQL Server registration properties
(by right-clicking on the (Local) node) and try again.

I performed the recommended action by the error message and set the registration properties as follows:

(Local) node registration properties are:
"Use SQL Server Authentication" is selected (I tried changing back to "Use Windows Authentication" and get the same error as above)

Under the "Options" section ...
- Group selected is - SQL Server Group (created by installation)
- Display SQL server state in console is checked
- Show system databases and system objects is checked
- Automatically start SQL Server when connecting is checked

At this point, everything I do to connect or change the database's properties results in the error message above.

Worth mentioning ... when I had the connection working, I tried to upsize an Access database to the SQL Server.  I tried doing that from a computer that was logged onto the server but using an account that is not a member of the domain (basically in Workgroup mode).  When the Upsizing Wizard tried connecting to the database, I got the same error on the machine I was upsizing from.  That's why I was changing to Mixed Mode.

We do need to get this going ASAP as this is a critical project.

Thanks for your help!

Question by:arnorite
    LVL 11

    Expert Comment

    Have you verified that SQL Server is running ?
    Check the Service Applet (Start -> Programs -> Administrative Tools -> Services)
    MSSQLSERVER. If it is running, restart the service.
    Go back to Enterprise Manager and remove the registration, then re-register the server

    Author Comment

    Yep, it's running and has been restarted about 153 times. :)

    As for the re-register issue, I thought I did that when I got the error I described above.  If it's what you're talking about, then I just keep getting the same error.  If you're talking about something different, can you please give me the details of what you mean?

    LVL 11

    Accepted Solution

    In client network utilities enabled Named Pipes as well as tcp/ip - move named pies to the top of the list. then try again.
    Can you connect using Query Analyser ?
    What value do you have under the Regsitry key
    For a Default instance:
    For a Named instance:
    HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\LoginMode

    Author Comment

    OK, I'm back!  Andrew, thanks so much for your help!  It turned out that the named pipes wasn't being selected or wasn't the first in the list.  Everything works now!

    LVL 11

    Expert Comment

    Sweet as mate, glad your sorted. Now get back to work :0)

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now