Can't Connect to SQL 2005 from VB 2005 Express.

Posted on 2007-10-03
Last Modified: 2013-11-27
Visual Basic 2005 Express:

I was successfully able to create a DataGrid and work with it using MS Access Tables.  I then tried to do the same thing with SQL 2005 tables and I can't connect.  I am able to Link my SQL tables to Access via ODBC and then use the linked table as a datasource in Visual Studio.

My SQL tables are on a different machine.  I have SQL 2005 on my machine, but the actual server instance runs from another box.

Here is what I am doing:

Add new datasource  .. Wizard comes up ... database ....New Connection  ...
Microsoft SQL Server Database File (SqlClient)
browse  .. find CABDATA.mdf ... open
I enter my password , etc.  then test the connection.  It fails blahblahblah may not allow remote connections.

When I look at the connection string generated by the wizard, the source is SQLEXPRESS
Data Source=.\SQLEXPRESS;AttachDbFilename=\\BigServer\data\CabData.MDF;Persist Security Info=True;User ID=sa;Connect Timeout=30;User Instance=True

Can I somehow change the connect string to make this work with my SQL 2005?

Microsoft SQL Server Database File (SqlClient)
Question by:Volibrawl
    LVL 16

    Expert Comment

    alter the configurations for SQL 2005 to allow remote connections
    LVL 18

    Accepted Solution

    Let's go through the connection string:

    Data Source=.\SQLEXPRESS;
    This is pointing to the local SQLEXPRESS instance on the local machine ... what you wanted was:
        Data Source=BigServer;
    When that is a named instance (like SQLEXPRESS), then it should have been
        Data Source=BigServer\SQLEXPRESS;

    ... what is it with connecting database files ??? Just not do that, it's a bad habit ... just connect to the database, not it's files. So replace this with:
        Initial Catalog=CabData;

    Persist Security Info=True;
    no comment here ...

    User ID=sa;
    OK, so you are willing to connect using the 'sa' account. Worst thing you can do, but let's forget about that for now. It is possible, but when you do login by means of SQL authentication, also mention the password. So change it to:
        User ID=sa;Password=MyLittleSecret;

    Connect Timeout=30;User Instance=True
    no comment on these either ...

    Bring them all together, and you end up with my advise:
        Data Source=BigServer;Initial Catalog=CabData;User ID=sa;Password=MyLittleSecret;Persist Security Info=True;Connect Timeout=30;User Instance=True;

    Hope this helps ...
    LVL 9

    Author Comment

    Thank you for valuable information, thus the points, however, the problem is not resolved.

    Upon further investigation, it would appear that the Express Version of Visual Studio will NOT ALLOW you to connect to SQL 2005, (only SQL Server Express and Access tables).  Unless someone has another opinion on this, I will assume I can't do what I am asking.

    LVL 18

    Expert Comment

    Glad I could be of any help ... sorry it didn't work out as I hoped !

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    With most software applications trying to cater to multiple user needs nowadays, the focus is to make them as configurable as possible. For e.g., when creating Silverlight applications which will connect to WCF services, the service end point usuall…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    728 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