PROBLEM: Installing MSDE and VB.Net app with Security 1

[Sprowler]

Hello all,

Hope you can help. I have a VB.Net App which connects to SQL Server database created in Server Explorer in VS.Net 2003. I have MSDE2000 running on my development pc and when I run the app in the IDE or as a Package it connects fine. I use "Mixed Mode" authentication, because I have read in a Microsoft KB that Windows Authentication will only work if there is a server available to do the authentication. As these will be standalone installations this isn't possible (?), so I have created the following ConnectionString using the System.Data.SQL Client SQLConnection Class: -

"Persist Security Info=False;uid=sa;pwd=(my MSDE Password);database=ccms;server=(local)"

I will be distributing this app to clients, so I need to install MSDE2000 on their pc (unless they already have it). I have setup MSDE2000 on a Test machine and installed it with the same strong SA Password as I did on my development machine, and copied over the ldf and mdf files into MSDE Data Directory, and installed my app from the Setup Project I created in VS.

However, when I attempt to open the database I get the following message: -

"System.Data.SqlClient.SqlException:Cannot open database requested in login 'ccms'. Login fails. Login failed for user 'sa'."

'ccms' is the name of the database I am trying to open. I have spoken with Microsoft about this, but I was wondering if anyone can help me, as it is seriously delaying me. I have a red hot client waiting to beta test it, and want to get it to them asap.

I have changed the Registry setting on the Test machine to allow "Mixed Mode" authentication (as advised by MS) but to no avail.

Thanks in advance for your help.

 

[AccordingToDale]

You might want to look at a couple things.

1) When you copy over the mdf and ldf files, this does not actually mean you can access the database. You have to attach it first. There is a stored proc called sp_attachdatabase (or something like that.

2) Secondly, if you are using the sa login to connect to the db, you will probably have to run the sp_fixlogin procedure as well. The SID for the sa on the created db will not match the SID for the sa on the MSDE instance.

My bet is the error is the first issue, but once you fix that you will run into the second issue.

Dale

 

[Sprowler]

Hi Dale,

Thanks for your prompt reply. I'll try it, and let you know.

Pity M$ couldn't have said this to me...

 

[Sprowler]

Hi again Dale,

Just tried your suggestion. The sp is in fact sp_attach_db and the syntax is :_

sp_attach_db @dbname=N'(the database name),@filename1=N'(Full file path to mdf file)',@filename2=N'(Full file path to ldf)'.

Couldn't find sp_fixlogin, but I've just run my app after just the above, and it has connected perfectly, so thanks very much for putting me on the right path.

If anyone's interested, there is a list of SPs at the following:-

http://www.msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_52oy.asp

You deserve a star, Dale. Have a nice day.

Sprowler

 

[AccordingToDale]

Hey Sprowler,

Glad it worked. I'm pretty new to this forum, so thanx for the first star! ;>)

The fix login script is

use dbName
exec sp_change_users_login 'Update_One', 'sysloginName', 'sysusersName'

Where sysloginName is the name of the login the User is given and sysusersName is the name of the database User. (Most typically these are the same although they don't have to be.)

What happens is that when you create a User, the User is given an SID, which is actually how SQL Server references all Users.

Where the fix login script comes in handy is a situation like this...

Let's say you have User "Joe" on a production box somewhere. Then you decide to create a new db on a dev box and want to mimic this User so you create a "Joe" on the dev box. Then, when you create your db on the dev box and add user "Joe" to the db, it works fine in the dev environment. But when you move the db to the production environment it craps out because the SID for the two different "Joe"'s is different.

Anyway, a shory explanation in a long reply just to say, 'Glad it worked.'

Dale

 

[Sprowler]

Hi Dale,

Sorry it took a while to reply. Thanks for the additional info, but hopefully I don't need it as users must log in to my app from a username and password stored in the db. I'm looking to hash the password data to secure it even more. I also expect to enable a web download/install rather than me on-site so hopefully this will be enough.

I've noted your extra info though and thanx again. Glad you liked the star too!

Sprowler