当前位置:主页 > 资料 >

Introduction to SQL Server Express User Instances
栏目分类:资料   发布日期:2018-08-03   浏览次数:

导读:本文为去找网小编(www.7zhao.net)为您推荐的Introduction to SQL Server Express User Instances,希望对您有所帮助,谢谢! By: || Related Tips:More Express Edition Problem You may have heard about a feature of SQL Server

本文为去找网小编(www.7zhao.net)为您推荐的Introduction to SQL Server Express User Instances,希望对您有所帮助,谢谢! www.7zhao.net



By: |  |   Related Tips:More >Express Edition

去找(www.7zhao.net欢迎您

Problem

You may have heard about a feature of SQL Server Express called User Instances that allows users who are not administrators on their local computers to attach and connect to a SQL Server Express database.  In this tip I will explain to you what User Instances are and how you can take advantage of them.

本文来自去找www.7zhao.net

Solution

Suppose you are a sysadmin or a DBA working in a large organization. It is not unusual that developers ask to install a local SQL Server Express instance on their desktop machines. They do so because they find it easier to test their own code on a separate environment in order to avoid overlapping their code with other team members or they may just want to test the code on a “clean” test environment without the delays involved in getting someone to create a new environment for them. 本文来自去找www.7zhao.net

But there is a problem with allowing people to install SQL Server Express instance on their machines, which is bigger issue at large organizations. The problem is that organizations (especially larger ones) must comply with security policies. Just to mention the most significant: copyright www.7zhao.net

  • General Data Protection Regulation (GDPR): .
  • Sarbanes-Oxley Act (SOX): .
  • Health Insurance Portability and Accountability Act (HIPAA): .

In order to comply with these policies, companies block USB and disk drive access to employees as well as the ability to install new software on their machines. The last is not just a security violation issue, but it may lead to economic penalties to the organization if an employee installed a piece of software without the respective license. All of these matters are the cause for companies to use the least-privilege user account (LUA) principle. copyright www.7zhao.net

SQL Server Express User Instances

A user instance is like a child instance of the SQL Server Express Database Engine. In other words, it is not a new instance of SQL Server Express, but it uses its resources. User Instances permits users who are not administrators on their local computers to attach and connect to a SQL Server Express databases without the need to attach the database to the SQL Server Express Database Engine. This allows each instance to run under the security context of each individual user. Something to note is that this feature is only available when using the .NET Framework Data Provider for SQL Server. 内容来自www.7zhao.net

The way User Instances work is by creating a virtual single user instance of SQL Server Express where the user has full administrative privileges without changing the security settings of the parent SQL Server Express instance. This virtual instance allows the user to access a copy of the master and msdb system databases and of course, the database specified on the connection string. But the database must be specified by the name of the mdf primary file. This feature was created with the idea to replace the Access/Jet model, where the database application simply connects to a file, and the user automatically has full permissions on all of the database objects without needing the intervention of a system administrator to grant permissions.

本文来自去找www.7zhao.net

According to what was previously said, you can’t connect to a remote User Instance. That’s why the connection must be done with named pipes provider.

去找(www.7zhao.net欢迎您

Also, if you want to use User Instances you must have the database files (.mdf, .ndf, .ldf) and not a backup file. You can’t have a User Instance without an mdf file and you can’t restore a backup on them because User Instances are not a full instance of SQL Server Express, it is a shortcut to mount a database and connect to it. 去找(www.7zhao.net欢迎您

User Instances are started when a user connects to it, if not already started. It has a lifetime of 60 minutes by default after closed. An Administrator can change this value by changing the “user instance timeout” setting as follows where n is the number of minutes between 5 and 65535: 内容来自www.7zhao.net

sp_configure 'user instance timeout','n'   

copyright www.7zhao.net

Another interesting behavior of User Instances is that they don’t run as a service, in a fashion that you must start or stop them manually; but also, they don’t behave like a user process. I know it is hard to understand, but think about this, the fact that they are started on a user process context, but keep open after that process ends during a timeout period is worth noting.

去找(www.7zhao.net欢迎您

When to use SQL Server Express User Instances?

You should use User Instances if you don’t have administrative permissions on your host machine and your application doesn’t need to share data. It could be the case that your application does need to share data, but you want to run tests without concurrency. 本文来自去找www.7zhao.net

For dedicated ASP.NET hosting using Windows Authentication. A single SQL Server Express instance can be hosted on an intranet. The application connects using the ASPNET Windows account, not by using impersonation.

www.7zhao.net

For ClickOnce project deployment, you can get more information about it here .

www.7zhao.net

When not to use SQL Server Express User Instances?

You should not use User Instances if you have administrative privileges on your system. In such case you must use a regular SQL Server Express instance or a SQL Server Developer instance installed locally.

copyright www.7zhao.net

If the database you need to connect is bigger than 10GB you cannot use User Instances or SQL Server Express. In such case you must use SQL Server Developer which is also free.

copyright www.7zhao.net

If your application uses more than one database on the same connection, like when you join data from different databases.

内容来自www.7zhao.net

User instances should not be used for third-party or shared hosting scenarios where all applications would share the same User Instance and would no longer remain isolated from each other.

本文来自去找www.7zhao.net

If your application runs on a version of SQL Server greater than 2012 you should useLocalDB because User Instances is going to be deprecated in future releases of SQL Server.

www.7zhao.net

Enabling User Instances

In order to enable/disable User Instances, a member of the administrators group, the SA account or someone with system administration rights on the SQL Server Express instance can change the status of User Instances by using the sp_configure stored procedure as follows. 本文来自去找www.7zhao.net

-- Enable user instances.  
sp_configure 'user instances enabled','1'   
RECONFIGURE WITH OVERRIDE
-- Disable user instances.  
sp_configure 'user instances enabled','0'
RECONFIGURE WITH OVERRIDE 
去找(www.7zhao.net欢迎您

Additionally, you must check that the SQL Server Express instance to handle the User Instances has the Named Pipes network protocol enabled. You can do this by looking at SQL Server Configuration Manager as shown below. 内容来自www.7zhao.net

内容来自www.7zhao.net

Connecting to a User Instance

In order to connect to a User Instance, you must create an ADO .NET Data Provider for SQL Server (SqlClient) object with a connection string as follows. 本文来自去找www.7zhao.net

Data Source=.\\SQLExpress;Integrated Security=true; User Instance=true;
AttachDBFilename= |DataDirectory|\app_data\Northwind.mdf; Initial Catalog=Northwind; 
copyright www.7zhao.net

Following is a table with a description of each parameter.

欢迎访问www.7zhao.net

Parameter Description
Data Source This is the parent instance of the User Instance.
Integrated Security Must be set to True to use Windows Authentication. User Instances don’t work with SQL Server Authentication.
User Instance Of course, must be set to true if you want to use this feature.
AttachDBFilename This is the path to the mdf database file.
|DataDirectory| This text is substituted with the path of the application that is opening the connection.
Initial Catalog The name of the database.

Next Steps

  • I suggest you read this tip in case you are not sure about using this approach: .
  • If you are not sure which version of SQL Server Express is the best for you, I suggest that you read my previous tip .
  • Check out the  for more cool tips and tricks regarding SQL Server Express Edition.
  • For new developments I suggest you to useLocalDB instead of User Instances. You can get more information on the next tip: .

Last Update:

内容来自www.7zhao.net

www.7zhao.net

内容来自www.7zhao.net

About the author

Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

欢迎访问www.7zhao.net

Related Resources 欢迎访问www.7zhao.net

去找(www.7zhao.net欢迎您


本文原文地址:https://www.mssqltips.com/sqlservertip/5593/introduction-to-sql-server-express-user-instances/

以上为Introduction to SQL Server Express User Instances文章的全部内容,若您也有好的文章,欢迎与我们分享! 内容来自www.7zhao.net

Copyright ©2008-2017去找网版权所有   皖ICP备12002049号-2 皖公网安备 34088102000435号   关于我们|联系我们| 免责声明|友情链接|网站地图|手机版