Changing a database from Windows Authentication to use Username and Password

[Elmo_loves_you]

Hi

I have just been informed by a few work collegues that the database system that I have created should now be set to use Server Username and Password.

Is it a big deal to change this without disrupting the current relationship of the database stored procedures and application code?

Or can it even be done? Would I have to set up the database again to use the server settings?

[LizR]

Depends how you setup the initial connection.

A lot of the time its just a short tweak, however, if the username/password is needed to be within a table within the database on the server you need a little more complex work, but, if you just mean to use username/password authentification to connect to the sql server it shouldnt be hard.

[Elmo_loves_you]

Depends how you setup the initial connection.

but, if you just mean to use username/password authentification to connect to the sql server it shouldnt be hard.

Yes initially I chose Windows Authentication to set up the connection to the database as I naively thought it shouldnt matter for developing the system, but I need to change it to use the Username and Password ... for that approach do I need an actual physical server to tell my database to connect to ? Or am I looking at this the wrong way?

[dickersonka]

No need for a physical server. For the database server change the security to use mixed mode authentication (sql server and windows authentication). Next, create the user(s) for your database. You might want to create a read only account, and an admin account. Then set security for your user. And set the connection string for your application to use a username and password rather than trusted or integrated.

[Elmo_loves_you]

Hiya,

Ok, so inside the 'Server Explorer' tab of the Visual Studio IDE I right clicked on the database.mdf file and clicked 'Modify Connection'. I then changed the radio button to 'Use SQL Server Authentication'.

I then typed in a 'new' user name and password and clicked 'Test Connection' but got an error message saying:

Login failed for user 'admin'. The user is not associated with a trusted SQL Server connection.

Can you point me in the right direction for this problem please.

[LizR]

Is that the username they gave you? or did they just tell you that you should be using a specific username and password?

It sounds like it didnt have the right username/password

[dickersonka]

You need to set the server to be in mixed mode authentication.

Go to properties for server, then click security and change it to mixed mode.

Then restart sql server.

If that doesn't allow you in, make sure the username is valid.

[Elmo_loves_you]

You need to set the server to be in mixed mode authentication.

Go to properties for server, then click security and change it to mixed mode.

Then restart sql server.

If that doesn't allow you in, make sure the username is valid.

OK am I missing something?

By server in mixed mode, what do you mean? At the minute I have no idea of which server the end result of this system will be loaded on to.

Properties of Server ?? -

Let me try and explain what I am doing. I am currently doing all my work inside Visual Studio. I created my SqlExpress Database by right clicking on the project name and adding a new item (the database).

Then I basically followed the defaults, keeping windows authentication until my database folder was displayed under 'Data Connections' in the 'Server Explorer' tab of Visual Studio.

Now if I right-click on the database name listed under 'Data Connections' and select 'Modify Connection' I get a pop up window informing me of the Data Source, Database file name (new or existing) and a group box containing 2 radio buttons. One says use 'Windows Authentication' and the other says 'Use Sql Server Authentication' and shows a Username and Password textbox. There is also a 'Test Connection' button which tells me that whatever I type in is not associated with Sql Server.

Does this mean that I have to get someone to create a new username and password on some server so that I can gain a successful connection??

Sorry, but Im new to this process and I would like to understand more about it but cant make much sense from documentaion on the net.

Kind Regards

[dickersonka]

The actual database server has properties of allowing sql authentication and windows authentication which you were using.

Are you able to open Sql Server Management Studio?
If you don't have it, here is the link
http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&DisplayLang=en

You right click on the database and select properties from there then security.

The concept is, you are authenticating inside sql server only and you don't need to be a windows user to gain access. That is why there needs to be a user if not present in the database. But first you need to set the database to allow this type of authentication.

Here is also a link on how to enable sql authentication with sql express if the option isn't there in management studio.
http://sqlservernotes.blogspot.com/2007/06/enabling-sql-authentication-in.html

[Elmo_loves_you]

Thank you VERY much Dickersonka :)

:S I will look into this ... I greatly appreciate the assistance you have given me.

Have a virtual hug lol :)

[dickersonka]

Lol virtual hug.

I normally use a developer's or server version of sql server, and not the express version.

I didn't realize it was a little different to set up sql authentication, that link should do the trick though.

You might want to look at getting the developer's version of sql server to save you many headaches like that, and its only $50 from microsoft.

http://www.microsoft.com/products/info/product.aspx?view=22&pcid=f544888c-2638-48ed-9f0f-d814e8b93ca0&crumb=catpage&catid=cd1daedd-9465-4aef-a7bf-8f5cf09a4dc0#HowToBuy