locked
SQL Server Express 2008 ADO connection parameters RRS feed

  • Question

  • I am brand new to SQL Server, and have a fresh, new installation of SQL Server Express 2008 on the server for a small LAN. I'm attempting to connect to it from a client PC via an ADO connection. My connection string is as follows:

    Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SampleData;Data Source=SERVER;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=GATEWAY;Use Encryption for Data=False;Tag with column collation when possible=False

    When I attempt to connect, I get the following error:

    SQL Server does not exist or access denied.

    How should I go about troubleshooting this?
    Monday, January 19, 2009 7:17 PM

Answers

  • By default, SQL Express is installed as a named instance called, appropriately enough, SQLExpress.  Is your Data Source=Server pointing to the full named instance?  Ex:

    Data Source=MyServer\SQLExpress

    Also, is the SQL Browser service running?
    Aaron Alton | thehobt.blogspot.com
    • Marked as answer by dongna Monday, January 19, 2009 8:54 PM
    Monday, January 19, 2009 8:01 PM
  • You may need to exit and reenter the connection wizard.  If this doesn't work, you likely don't have access to the database, in which case your Windows user is not a local admin.  To resolve this, select your login in SSMS, right-click and go to "properties".  Under Server Roles, select an appropriate role.  In your case, it sounds like you're on a dev machine, so sysadmin should be fine.

    HTH....let us know how you make out.
    Aaron Alton | thehobt.blogspot.com
    • Marked as answer by dongna Monday, January 19, 2009 8:54 PM
    Monday, January 19, 2009 8:36 PM

All replies

  • By default, SQL Express is installed as a named instance called, appropriately enough, SQLExpress.  Is your Data Source=Server pointing to the full named instance?  Ex:

    Data Source=MyServer\SQLExpress

    Also, is the SQL Browser service running?
    Aaron Alton | thehobt.blogspot.com
    • Marked as answer by dongna Monday, January 19, 2009 8:54 PM
    Monday, January 19, 2009 8:01 PM
  • No, my Data Source setting was Data Source=SERVER. I changed it to Data Source=SERVER\SQLExpress and that seems to have worked.

    Now I am trying to specify a particular database to connect to (as indicated by the Initial Catalog=SampleData in my connection string provided in my initial post). I'm using a "Build Connection String" dialog to build my connection string, and now that I have the server specified correctly, it's showing me three available databases to choose from:

    master
    msdb
    tempdb

    In looking in the SQL Server Management Studio, I see these three databases shown under Databases\System Databases. I am unable to create a new database in this location (nor, it would seem, should I want to). I have created my database named SampleData under Databases. But there seems to be no way to specify this location/database in the connection string. Do you have any ideas?

    Thank you for your help!
    Monday, January 19, 2009 8:32 PM
  • You may need to exit and reenter the connection wizard.  If this doesn't work, you likely don't have access to the database, in which case your Windows user is not a local admin.  To resolve this, select your login in SSMS, right-click and go to "properties".  Under Server Roles, select an appropriate role.  In your case, it sounds like you're on a dev machine, so sysadmin should be fine.

    HTH....let us know how you make out.
    Aaron Alton | thehobt.blogspot.com
    • Marked as answer by dongna Monday, January 19, 2009 8:54 PM
    Monday, January 19, 2009 8:36 PM
  • Yes, I had to click the checkbox sysadmin for my logon, and then I was able to see my database and connect.

    Thank you again for your help!
    Monday, January 19, 2009 8:53 PM