encrypting your sprocs and using 'user instances' in sql 2005 can make
this quite easy to do.
i mean; all you need to do is add something in the connection string
right??
-Aaron
http://msdn.microsoft.com/sql/express/default.aspx?pull=/library/en-us/dnsse/html/sqlexpuserinst.asp
Introduction
A design goal for the new Express Edition of SQL Server 2005 was to
implement much tighter integration with Visual Studio database design
features. The Visual Studio project system is very good at managing the
set of files that make up the project. To fit smoothly into this
toolset, SQL Server databases must be manipulated as files.
Fortunately, a SQL Server database is a collection of files, so
managing the file collection as part of your Visual Studio project is
not complicated. Unfortunately, connecting to those files as a database
from your application is not as simple and automatic as it ought to be.
The User Instance feature makes that operation easier.
Attaching Database Files
Understanding user instances in Microsoft SQL Server 2005 is easier if
we understand the problems they were designed to solve. This section
describes attaching database files to a SQL Server database
instance-one of the things that user instances make easier.
For the purposes of this paper, there are two types of SQL Server
database files (there are more, but for our purposes, two is enough).
These are data (.mdf) and log (.log) files.
The file that contains the database data has an .mdf file name
extension-for example, AccountsReceivable.mdf is a data file. Each
data file has a corresponding log file that contains the transaction
log. The log file has an .ldf file name extension. So, for example, the
log file for our database would be named AccountsReceivable_log.ldf.
These two files are very tightly coupled to each other. The database
file contains information about the exact version of the log file. If
you restore the data file from backup without restoring the same
version of the log file, the database won't start. When you manipulate
the database files in your project, it is important to treat these two
files as a matched set. For example, if you revert to a previous
version of the .mdf file, you must also revert to the same version of
the .ldf file.
Before you can connect to a SQL Server database, the server must know
about the database files. The server opens the files, validates the
version, makes sure the log file matches the database file, and does
any recovery operations necessary to get the database file synchronized
with the log file. The process of letting a server that is running SQL
Server know about a database file is called attaching the database. If
Sally has a database file that she needs to access through SQL Server
on an enterprise server, she gives the .mdf and .ldf files to her
database administrator (DBA). The DBA will:
Attach the database files to the server by using a CREATE DATABASE ...
FOR ATTACH command.
Create a login for Sally on the server.
Create a user for Sally's login in the database.
Grant the user the permissions that Sally requires to run her
application.
This amount of effort makes sense if this is a major application, but
if Sally is a developer building applications on her own machine, this
amount of work may be excessive. Note that if Sally is a member of the
Administrators group on the computer where the SQL Server instance is
running, the last three steps aren't necessary. This is because an
administrator can always log in and has administrator rights on all
databases attached to the server.
AttachDBFilename
Fortunately, the SQL Server client code includes an option called
AttachDBFilename, which eliminates the need to have a DBA attach the
database files to a server before they can be used. When the
AttachDBFilename keyword is included in a connection string, the
specified file is attached to the SQL Server instance and the client is
connected to the newly attached database. The argument to the
AttachDBFilename option is the name of the file to attach. Here is an
example.
AttachDbFilename=|DataDirectory|\Database1.mdf;
The |DataDirectory| is a shortcut for the directory where the program
opening the connection lives. To attach a file in a different
directory, you must provide the full path to the file. The log file in
this case is named Database1_log.ldf and is located in the same
directory as the database file. If the database file is already
attached to the SQL Server instance, the connection is opened to the
existing database.
This is a great option because if you are an administrator, you can
attach and connect to a database file by specifying the file name in
the connection string in your application. Many developers run as
administrators on their systems so AttachDBFilename works well for
them. The problem is that Microsoft strongly recommends NOT running as
an administrator because this reduces the amount of damage that can be
inflicted by a virus. What we need in this case is a way to use
AttachDBFilename without being a member of the Windows Administrators
group. The solution is the User Instance feature.
User Instances
I have mentioned SQL Server instances several times without defining
what they are. A SQL Server instance is a SQL Server executable program
running on a server. Each instance has a name, a sqlservr.exe process
in memory, buffer memory, its own copies of the system databases, and
its own set of user databases. By default, SQL Server Express installs
as an instance named "SQLEXPRESS," for example. You connect to a named
instance by specifying the instance name with the server name in the
connection string. That is why you normally specify ".\SQLEXPRESS" as
the server name when connecting to a local SQL Server Express database.
The dot means the local server and \SQLEXPRESS specifies the SQLEXPRESS
named instance. The SQL Server service (sqlservr.exe) runs as a
Microsoft Windows service and executes in the user context of the user
specified as the service account in the Windows service manager. For
SQL Server Express, this account defaults to "NT AUTHORITY\NETWORK
SERVICE" although a different account can be specified during setup.
SQL Server Express expands the concept of SQL Server instances by
supporting user instances. A user instance is similar to a normal
instance but it is created on demand while normal instances are created
during setup. The service account for a user instance is the Windows
user who opened the SQL Client connection to the database. In other
words, if Sally opens a connection to a database file specifying the
user instance option in the connection string, the user instance will
have Sally as the service account.
User instances are created when the User Instance option is set in the
SQL Client connection string. Following is a section of a Visual Basic
configuration file that shows the User Instance option.
<connectionStrings>
<add name="TestVB1.Settings.Database1ConnectionString"
connectionString="Data Source=.\SQLEXPRESS;
AttachDbFilename=|DataDirectory|\Database1.mdf;
Integrated Security=True;
User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
The following are a few things to notice in the connection string.
The data source is .\SQLEXPRESS. User instances are created by the
parent SQL Server Express instance, so the initial connection must
specify the parent instance.
AttachDBFilename is used to specify the database to attach to the user
instance.
Integrated Security is True. User instances only work with Integrated
Security-the SQL Server users with user name and password don't work.
The provider name is System.Data.SqlClient. The User Instance option is
only valid on SqlClient connection strings.
When a connection with this connection string opens successfully, the
user application is connected to a user instance of SQL Server Express
running as the user who opened the connection. The user is connected to
the database in the "database1.mdf" file. If Sally opens this
connection, the user instance runs with Sally as the service account.
Because Sally is the service account for the instance, Sally has full
administrator rights to all databases attached to the user instance
even if she isn't a Windows administrator. That is why the
AttachDBFilename option works even though Sally is a normal user.
Opening a User-Instance Connection
What happens when you open a connection with the User Instance option
set to True? The following steps describe what happens if this is the
first time the user has opened a user instance connection.
The SQLClient logic opens a connection to the parent SQL Server Express
instance (.\SQLEXPRESS by default).
SQL Server Express detects that the User Instance option is set and
that there is no user instance for this user.
The master and msdb system database files are copied to the user's
directory. In Sally's case, the directory will be:
C:\Documents and Settings\Sally\Local Settings\Application
Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS
These files are copied from a template directory that is created when
the parent instance is installed. When the user instance starts, the
tempdb, log, and trace files are written to this same user directory.
The parent instance impersonates the Windows user who is opening the
connection and starts a copy of sqlservr.exe running as that user. The
location of the system databases is passed as a parameter. The name of
the instance is generated. For example: 69651E0A-5550-46.
A Named Pipe database connection is established for the new instance.
The name is based on the instance name. For example:
\\.\pipe\69651E0A-5550-46\tsql\query.
The database file specified in the AttachDBFilename parameter is
attached to the new instance and named with the full path to the file:
[C:\MYDBPROJECTS\TESTVB1\TESTVB1\DATABASE1.MDF]
The name of the named pipe is passed back to the SqlClient that is
opening the connection.
When SqlClient receives the name of the connection, it closes the
connection to the parent instance. It opens a new connection to the
user instance using the returned named pipe name.
Once the user instance has been created for a particular user, the
system databases and the named pipe are kept around. Therefore, after
the first connection, subsequent connections just do the last two
steps.
The sqlservr.exe process that is started is kept running for a while
after the last connection to the instance is closed. Therefore, it
doesn't need to be restarted if another connection is opened. The
length of time it stays around is set by the sp_configure option "user
instance timeout". By default, this is set to 60 minutes but you can
use the sp_configure command to change this.
SSEUtil
One indispensable tool for working with user instances is SSEUtil. It
opens a user instance and allows you to execute SQL commands against
the user instance. It can also detach a user instance so that you can
work with the files. There are many other features available and
SSEUtil gets better and more capable with every release. You can
download it from the SQL Server Express Utility Web site.
Some of the things you can do with SSEUtil are as follows:
Attach and detach databases.
Run SQL Statements and execute SQL batch files.
List and connect to child instances.
Execute the Checkpoint command and shrink a database.
Sign a database.
Off By Default Configuration
For security reasons, SQL Server 2005 has several features that are
turned off by default to reduce the surface area of code available for
a hacker to attack. A tool that allows you to configure these options,
called the Surface Area Configuration (SAC) tool, is installed with SQL
Server Express. Changing the configuration by using the SAC tool
changes the settings of the parent instance but does not affect any
user instances. This allows each instance to turn on only the options
required for the applications in that instance.
Since the SAC tool does not configure user instances, we must fall back
on our friend SSEUtil to do this configuration. The only option that is
generally required in user instances is the "clr enabled" option, which
is needed if your application includes CLR stored procedures, triggers,
user defined types, etc. The following figure shows how to turn on the
"clr enabled" option.
C:\SSEUtil>sseutil -c
Console mode. Type 'help' for more information.
1> sp_configure 'clr enabled','1'
2> go
Command completed successfully.
1> reconfigure
2> go
Command completed successfully.
For the sake of completeness, there are two other options that turn on
OLE Automation and xp_cmdshell. I don't recommend using either of these
options. A CLR stored procedure generally does what these procedures do
except in a safer, more reliable manner. If you really need to use
these options, here is how to use SSEUtil to turn them on.
C:\SSEUtil>sseutil -c
Console mode. Type 'help' for more information.
1> sp_configure 'show advanced option', '1'
2> go
Command completed successfully.
1> reconfigure
2> go
Command completed successfully.
1> sp_configure 'xp_cmdshell', '1'
2> go
Command completed successfully.
1> reconfigure
2> go
Command completed successfully.
1> sp_configure 'Ole Automation Procedures', '1'
2> go
Command completed successfully.
1> reconfigure
2> go
Command completed successfully.
Connecting to User Instances from Other Clients
Creating a user instance and starting the user instance process can
only be done from an SqlClient connection in a Microsoft .NET Framework
application. Once the user instance is running, however, any client
that can connect to a named pipe can connect to the user instance by
opening a connection to the named pipe that is created for the user
instance. The name of the named pipe is available from the
sys.dm_os_child_instances view in the parent instance. Use the
following statement.
SELECT owning_principal_name, instance_pipe_name
FROM sys.dm_os_child_instances
Figure 1 shows the contents of a typical sys.dm_os_child_instances
view.
Figure 1
Once you know the name of the named pipe, you can put np: in front of
it and use it in your connection string. For example, here is an
extract from an SQLCMD.exe session.
C:\>sqlcmd -S np:\\.\pipe\69651E0A-5550-46\tsql\query
1> use [C:\MYDBPROJECTS\TESTVB1\TESTVB1\DATABASE1.MDF]
2> go
Changed database context to
'C:\MYDBPROJECTS\TESTVB1\TESTVB1\DATABASE1.MDF'.
1> select * from test1
2> go
Col1 Col2 Col3
------ ---------- -----------------------
1 Frank 2005-02-11 00:00:00.000
12 Sam 2001-03-21 00:00:00.000
This named pipe is a local-only named pipe so there is no way to attach
to a user instance from a remote client. Also, keep in mind that
attaching directly to the named pipe only works if another application
that used an SqlClient connection has started the user instance within
the last hour.
User Instance Limitations
The unique User Instance architecture introduces some functional
limitations as follows:
Only local connections are allowed.
Replication does not work with user instances.
Distributed queries do not work to remote databases.
User instances only work in the Express Edition of SQL Server 2005.
Common Issues
The User Instance architecture sometimes leads to confusion when
databases don't behave the way we are accustomed to. Most of these
issues are related to the database files that get attached to the user
instance and how they are handled. Following are the more common
issues.
The user instance cannot attach the database because the user does not
have the required permissions. The user instance executes in the
context of the user who opened the connection-not the normal SQL
Server service account. The user who opened the user instance
connection must have write permissions on the .mdf and .ldf files that
are specified in the AttachDbFilename option of the connection string.
One common issue occurs when working with the Visual Web Designer. The
application connects to a user instance database from the Visual Studio
integrated development environment (IDE) and then fails to connect when
the database is opened by the Web page. When the ASP page opens the
database it is generally running as ASPNET. If ASPNET does not have
write permissions on the database files, the connection fails.
Another common issue is when you open a database file successfully when
the database is attached to the SQL Server Express instance, but fails
when you try to open it from the Visual Studio IDE. This might occur
because the SQL Server Express instance is running as "NT
AUTHORITY\NETWORK SERVICE," while the IDE is running as you. Therefore,
the permissions may not work.
A variation of this issue is when the user that opens the user instance
connection has read permissions on the database files but does not have
write permissions. In this case, SQL Server attaches the database as a
READ_ONLY database. If you get a message saying that the database is
opened as read only, you need to change the permissions on the database
file.
The other main issue with user instances occurs because SQL Server
opens database files with exclusive access. This is necessary because
SQL Server manages the locking of the database data in its memory.
Thus, if more than one SQL Server instance has the same file open,
there is the potential for data corruption. If two different user
instances use the same database file, one instance must close the file
before the other instance can open it. There are two common ways to
close database files, as follows.
User instance databases have the Auto Close option set so that if there
are no connections to a database for 8-10 minutes, the database shuts
down and the file is closed. This happens automatically, but it can
take a while, especially if connection pooling is enabled for your
connections.
Detaching the database from the instance by calling sp_detach_db will
close the file. This is the method Visual Studio uses to ensure that
the database file is closed when the IDE switches between user
instances. For example, you are using the IDE to design a data-enabled
Web page. You press F5 to run the application. The IDE detaches the
database so that ASP.NET can open the database files. If you leave the
database attached to the IDE and try to run the ASP page from your
browser, ASP.NET cannot open the database because the file is still in
use by the IDE.
Distribution and Deployment
One of the attractive features of user instances is that you can
distribute your database application by including the database files on
the media with your application code. If the database files are copied
to the same directory as the application and |DataDirectory| is used in
the AttachDbFilename option of the connection string, the database will
work no matter where the application is copied to as long as there is a
SQL Server Express installation available. If the user doesn't already
have SQL Server Express, you can either include SQL Server Express with
your application or your users can download it from the Web. No
additional configuration is required to support user instances.
RANU
Every software feature needs a cool acronym. The logical acronym for
user instances, UI, is already taken, so the user instance is known as
RANU for Run As Normal User. The official name is the User Instance
feature, but you will often hear the feature referred to as RANU.
Converting a User Instance Database
You might want to change your user instance database into a normal SQL
Server database. The primary reasons to do this are if you want
multiple users to connect to the database or if you need to upgrade to
another version of SQL Server-perhaps because you are running into
performance or database size limitations. While there are several ways
to do this, the easiest way is to attach the database files to a normal
SQL Server instance by using the CREATE DATABASE ... FOR ATTACH
command. Then remove the AttachDbFilename and User Instance options
from the connection string. Remember that while you are connected to
your own instance of SQL Server, you have full administrator
permissions on the databases that are attached to that instance. When
you change to a normal SQL Server instance, each user must have a login
and database user. The database user must be granted the permissions
required to run the application.
Conclusion
SQL Server 2005 Express Edition includes a new User Instance feature,
which allows an application to open a database connection if the name
of the file where the database data is stored is specified. This works
even if the user who is opening the connection is not an administrator.
This feature simplifies creating and distributing .NET-connected
database applications.
