Using SQL Server Authentication on SQL Express

For most of my databases, I am content to use Windows Authentication for my database access.  In a development or small shop environment, it's usually acceptable to let Windows bear the burden of authentication.

But if you need to add just one database app that requires a separate login beyond the Windows login, here's how.



First a few definitions.

SERVER vs. DATABASE

SQL Server (in my case express 2008 R2)  is not a Database.  In the management studio, when you log into the management studio, you can see that .\SQLEXPRESS (the root node) is called a server.  It is important to read this article carefully when I talk about servers vs. databases, as it makes a difference.



LOGIN vs. USER

This is a little more blurry, but a login is not much more than a name, password, and a set of permissions to access the server.  A user exists in databases, and has specific roles and permissions in an individual database.



Getting Started

In the Mode

First, make sure your server is in the mode (mood?) to use both kinds of authentication.

Open SQL Server Management Studio and right-click the SERVER name.  Pick properties.

Then on the Security tab, make sure SQL Server and Windows Authentication mode is selected.

If it wasn't, then you'll need to save the change and restart SQL Server.  If it was already selected you can skip this section.

Restarting SQL Server

Return to the SQL Server Management Studio and right click the Server Name.

Select restart.



Create a Server Login

When we create logins, in general we want to create one login per user. First, log into management studio using windows authentication (or however you normally gain admin access).  Under the server tree (not a database tree) select Security- Logins and pick New Login.

Add your login... 

Make sure to add a password and select the default database.

 

On the User Mapping tab...

Select the checkbox by your database.

Enter dbo as the default schema

Check every permission that doesn't contain the word deny.

Note that there are some additional roles in my database that I had added previously.

 On the Status tab, make sure grant and enabled are selected.

Click OK.

Using witch-hazel and fairy dust, Management studio will now create your LOGIN to the SERVER, and your USER in the DATABASE.



Look in the Databases tree and find the user created by the wizard.

Open his properties.

The General tab should look much like this.



Testing

To test your new user/login, open a second copy of SQL Server Management Studio.

Change to SQL Server Authentication and enter your user login info.

If you forgot the password (as I did about 6 times while writing this article) you can go back to the login tree in your first SQL Server Management Studio window and change it there, then try again.



Now it's easy to use this login in your programs to access this database.  It's also easy to set database level permissions, roles etc.



in C# to create a connection string to access the database do this:

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

C# Can't Connect to MSSQL Express: File in use by another process

Ok, I created MSSQL express database in SQL Server 2005 Express.

Now I opened C# 2005 Express and opened my project and tried to connect to the database.

FIRST OF ALL:

Why oh WHY aren't all the .SQLEXPRESS databases sitting there in an easy dropdown list? You have to hunt them down like wounded gazelles on the serengeti.

SECOND:

After hunting down the file (c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AFIDatabase.mdf) and going thru this sequence in the C# Express menus...

Data - Add New Data Source - Database

New Connection - Microsoft SQL Server Database File

Browse - "c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AFIDatabase.mdf"

Test Connection:

I get this...

---------------------------

Microsoft Visual C# 2008 Express Edition

---------------------------



Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AFIDatabase.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".

An attempt to attach an auto-named database for file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AFIDatabase.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Now. BEFORE you tell me to make sure SQL Server Express is closed, please note that I already DID that, ALSO, MSSQL is supposed to be MULTIUSER, which should allow SHARING anyways.

The HELP is USELESS.

Can anyone help me?

---

OK. Here's what I was doing wrong. I was trying to connect to a file on the drive, not the SQL Server Express Service. Here is the correct way.

1. Open C# 2008 Express
2. From the menu: Data: Add New Data Source.
3. Select the Database icon and Next>.
4. New Connection, then Change.
   
5. Select Microsoft SQL Server Database File and OK.
6.