[RESOLVED] Can't connect to SQL Server Express 2008 database

[weirddemon]

When trying to connect to a SQL Server Express 2008 database, I receive the following error:

Failed to generate a user instance of SQL Server. Only an integrated connection can generate a user instance. The connection will be closed.

Which occurs on my "connection" line:

Dim connection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=dbISTme!.mdf;Integrated Security=False;User ID=test;Password=test1;User Instance=True")
 
        Dim command As New SqlCommand("SELECT COUNT(*) FROM Credentials WHERE UserID = @UserID AND Password = @Password", _
                              connection)
 
        With command.Parameters
            .AddWithValue("@UserID", Me.txtUserName.Text)
            .AddWithValue("@Password", Me.txtPasssword.Text)
        End With
 
        connection.Open()
 
        If CInt(command.ExecuteScalar()) = 0 Then
            
        Else
            Me.DialogResult = Windows.Forms.DialogResult.OK
        End If
 
        connection.Close()]

I've worked with SQL CE databases in the past and I've never had a problem with them. The connection strings are a little bit different, so I think that might have something to do with it.

Originally, I looked up that error and got a whole bunch of nonsense. I found two solutions that worked for most people, but they did not work for me. The fist was an issue with XP and SP2, which I'm Windows 7. The second resolution was to delete the SQLEXPRESS folder.

Then I saw a post from another site:

One thing to remember is that although user instances is the default when working with Visual Studio, it is not mandatory to use them, it is quite possible to attach to databases in the traditional way from SQL Express, to do this you just need to edit the connection string and delete the "user Instance=true". User Instances just make it easier (when it does work) to work with databases as though they were files.

So, I deleted that line and got another error:

An attempt to attach an auto-named database for file dbISTme!.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

This seems like the most generic error ever. There's even less information about this error.

I've made sure that the database uses the SQL authentication and not Windows and made sure my user, "test", has the correct permission.

Anyone have any on ideas on what I'm doing wrong?

Thanks

[techgnome]

The problem is exactly what the error is telling you... you are trying to attach the mdf file in a user instance, but at the same time you told it to not use integrated security, but SQL security... which you can't do... IF you are in fact trying to attach the database in a user instance, you MUST use integrated security.

The second error (about the db already existing) happened because quite literaly, the DB already does exist.

Sounds like you'r trying to connect to a database that already exists on the sql server ... if that's the case, then just connect to it... don't specify a user instance and don't give a path to the mdf... just use a normal connection straight up.


As a side note, where did you find that quote? I've seen that more than once now, and I don't think it's accurate information (regarding user instances being the default).

-tg

[weirddemon]

Alright. I didn't realize that I was trying to attach it each time than just connect to it

I changed my connection string to:

"Data Source=.\SQLEXPRESS;Initial Catalog=dbISTme!.mdf;User ID=test;Password=test1;"

Since I no longer need to attach the database, I assume it's okay just to remove the Integrated Security line. I no longer receive either issue once I remove those two lines and just connect to the DB.

However, I'm getting another issue:

Cannot open database "test.mdf" requested by the login. The login failed. Login failed for user 'test'.

Is there a specific way that the user needs to be setup? I made sure the credentials matched up.

I setup the user with these settings:



Uploaded with ImageShack.us

And I got that quote from here.

[techgnome]

you';re specifying the filename.... don't... just the name of the database.... dblSTme! .... what's with the ! in the name?

-tg

[weirddemon]

you';re specifying the filename.... don't... just the name of the database.... dblSTme! .... what's with the ! in the name?

-tg

When I remove the extension, I receive the same error. The exclamation point is part of the database name. It's part of the app name as well, so I thought to just include it. I suppose it's not really needed either way... but yeah

[techgnome]

huh... well, that would imply that the user name or password is wrong.... are you *sure* the password is right?

-tg

[weirddemon]

huh... well, that would imply that the user name or password is wrong.... are you *sure* the password is right?

-tg

Eureka! The password was correct, but it turns out that I needed to map the database to the user. I did not know that. I just started playing around with the settings and figured it out.

Thanks for your help, tg!





Uploaded with ImageShack.us

[GaryMazzone]

No you don't have to map the database to the user you grant the user permission on the database. You do that by adding the login to the database security tab.

[weirddemon]

No you don't have to map the database to the user you grant the user permission on the database. You do that by adding the login to the database security tab.

Ah. Well... that would make sense too. I escalated the user's permission and mapped the database at the same time and attributed the resolution to the mapping. I suppose that's a faulty syllogism

Thanks for clearing that up, Gary