Tech Off Thread

13 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Making a simple connection with SQL 2005 wont work!!

Back to Forum: Tech Off
  • User profile image
    Deactivated User

    Comment removed at user's request.

  • User profile image
    geekling

    Full exception text?

    Off the top of my head, I think you're not pointing it at the sql server instance correctly. Maybe .\MACHINE_NAME\SQLExpress instead of just .\SQLExpress.

    That's how mine was configured by default, anyway.

  • User profile image
    Ang3lFir3

    if there is a .ldf file with the same name in the directory and "database" is set you won't be able to attach the db and will recieve an exception..... make sure there is no ldf and drop the "database" and replace it with

    Initial Catalog=Database1

    see if that helps any...... otherwise pasting the actuall exception message would be helpful to figure out whats wrong.......

    you can use a SQLConnectionStringBuilder object if you want to make the connection string more dynamic...and eliminate a bunch of nasty concats

  • User profile image
    Deactivated User

    Comment removed at user's request.

  • User profile image
    littleguru

    The SQL Server user does not have the rights to access the directory with the database file. The exception message seems to try to tell you that:

    (Access denied).

  • User profile image
    borosen

    Have you attached the db via the SQL Management studio?

    In that case, detach it and make sure you mark 'Drop connections'; 
    now the db is available for you to use again.
    (You might have to restart your development environment).

    I don't know what is going on, but this is my experience.

  • User profile image
    Deactivated User

    Comment removed at user's request.

  • User profile image
    borosen

    Try adding 'user instance=true;' to your connection string. (Without the ')

    If it still does not work, create a new db with another name and try again.

  • User profile image
    Deactivated User

    Comment removed at user's request.

  • User profile image
    borosen

    The 'user instance' causes a new instance of the sql express beeing started, running with the callers credentials.

    Database:
    A sql instance can have more than one database or catalog attached, to distinguish these databases, they are given names.


    Initial catalog:
    When you connect to the sql instance, this property specifies which of the named databases the caller should initially be connected to. (See above)


    If you want to specify userid and password, then you should not use the 'user instance' property.
    You should also assign permissions, preferably? via the Sql management studio.
    Make sure the primary instance of sql express has read/write access to your mdf file. (The primary instance normaly runs with another sets of credentials than the users account.)

  • User profile image
    blowdart

    borosen wrote:

    Make sure the primary instance of sql express has read/write access to your mdf file. (The primary instance normaly runs with another sets of credentials than the users account.)


    And by default it runs as "Local Service" whch is very limited to what it can do and where it can access (which is why it can't get to "Documents and Settings"). It's not a good idea to change this unless you really have to, SQL should be run with as narrow a set of privileges as possible.

  • User profile image
    Deactivated User

    Comment removed at user's request.

  • User profile image
    borosen

    Winston Pang wrote:
    

    Well besides using SQL server studio management how else can u set a user id and password?

    Sql server studio manager is freely downloadable. I would propose using it.
    Otherwise, you could use DDL and system stored procedures, but now we have left a simple connection far behind.

    Winston Pang wrote:
    

    So the initial catalog is basically saying if "database1" is attached to the sql instance already, then just read from that? What happens if the database name doesn't exist for the initial catalog value u set, does it then attach itself to the instance?

    Try it! Smiley

    Winston Pang wrote:
    

    So how do i adjust privlidges on the mdf file or the SQL server itself so that you can locally connect to a DB without using the user instance property?

    Just use the ordinary security settings for files.

    As Blowdart mentioned above, the permissions on files in the user directory are usually quite restrictive, take care of where you put the file and how you assign permissions.

    Winston Pang wrote:
    
    Also one last question, typcially how are things setup when people use SQL server as their backend for their applications?
    Do they just leave the .mdf file on the users machine, and specify the Server path as some other remote path?

    AFAIK sql express does not support having data files on a share.

    If this is a database deployed together with your application, I bet the normal way of connecting to the database is through user instance.

    Winston Pang wrote:
    
    Also, how when you specify the SQL Server instance name, how is that obtained normally? I mean yeah it's common that an SQL Express install is named as SQLExpress, but what about other versions, and what if someone renamed it, what happens in terms of deploying an application, can you dynamically determine if an SQL Server install is present and detect the named instance?

    If that could be an issue for you, you could ask the user when the application is installed.
    In a hosted environment, i.e. web-server, the sys-admin of that server should know.


    Edit.

    My suggestions above is targeted SQL Server express.

    If you develop for something that should be deployed on SQL server, I think you should go for creating a database from within sql management studio on the default instance and connect to that instead of via an attached database file.

    I can recommend this site for short info on connection strings.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.