保护LocalDB免受用户访问 [英] Protect LocalDB from user access
问题描述
我正在考虑使用SQL LocalDB作为客户端数据库，它将替代在.net 4中开发的内部应用程序中的当前SQL CE 3.5数据库.
I'm looking at using SQL LocalDB as the client side database which will replace the current SQL CE 3.5 database inside an in-house application developed in .net 4.
我遇到了一个不确定的问题，那就是LocalDB实例的安全设置.
I've come across a problem that I'm not sure how to get around and that's with security setup of the LocalDB instance.
如果我在.net中设置脚本来创建私有LocalDB实例，例如(localdb)\ T1，然后在该实例中创建一个新数据库，再添加一个SQL用户帐户+密码(非域帐户)，如何阻止具有管理员级别特权的本地Windows用户(例如我自己的AD帐户)访问使用SSMS的"T1"实例+数据库?
If I setup a script inside .net to create a private LocalDB instance, e.g. (localdb)\T1, then create a new database inside that instance plus add a SQL user account + password (non domain account), how do I stop the local windows users (like my own AD account) with admin level privileges from accessing the 'T1' instance + database using SSMS?
我可以看到一个场景，其中部署了应用程序，然后我们有一些精通IT的用户偷偷摸摸地决定安装SSMS并使用其Windows帐户连接到(localdb)\ T1.她对数据库的完全访问权限，而这正是我要阻止的事情.
I could see a scenario playing out where we deploy the application, then we have some IT savvy user who goes snooping around and decides to install SSMS and connect to (localdb)\T1 with their windows account, which would give him/her full access to the database, which is exactly what I'm trying to stop from happening.
我们的一些员工可以在不连接域的情况下进行远程工作，因此我们向他们授予其PC的本地管理员权限，以便他们可以安装软件，因此，即使我可以阻止分配给他们的Windows登录名，也无法阻止他们设置一个新的本地管理员帐户并使用该帐户登录，然后打开SSMS，然后访问数据库.
Some of our staff work remotely with no connection to a domain so we give them local admin rights to their pc so they can install software, so even if I could block their assigned windows login name, there would be nothing stopping them from setting up a new local admin account and logging in with that, opening SSMS then accessing the database.
对此，任何指针将不胜感激！
Any pointers on this would be greatly appreciated!
推荐答案
实际上，它并不难.使用本地管理员帐户在客户端计算机上安装SQL Server.为了使您的生活更轻松，请使用混合身份验证.
In fact, it shouldn't be so hard. Install SQL Server on client machine using local admin account. To make you life easier, use Mixed Authentication.
安装实例后，本地管理员帐户应分配sysadmin服务器角色(这是正常的安全设置).现在，使用以下命令:
After you have your instance installed, local admin account should have sysadmin server role assigned (that's normal security settings). Now, use the following:
-
创建新的SQL Server用户，并将sysadmin服务器角色分配给该帐户.关闭SSMS并使用新凭据登录.或者只是使用sa帐户(不是很好的做法，但在这种情况下可以).
Create new SQL Server user and assign sysadmin server role to that account. Close SSMS and log-in using new credentials. Or simply use sa account (not so good practice but in this case it's OK).
转到安全性-登录"，然后从本地管理员中删除sysadmin角色.另外，请签入用户映射，并从本地管理员那里撤消"所有权限.
Go to "Security - Logins" and remove sysadmin role from local admin. Also, check in user mappings and take all rights "away" from local admin.
作为测试，尝试以本地管理员身份登录，我想您将无法登录服务器，因为该用户没有家庭"数据库.但是，即使您可以登录，它也只有公共"角色.
As a test, try to log-in as local admin, I guess you won't be able to log-in into server because there is no "home" database for that user. But even if you can log-in, it has only "public" role.
由您决定安装数据库并保护数据库的安全-将权限分配给您选择的用户.
It's up to you to install your database and secure it - assign permission to user of your choice.
关于您担心本地管理员可以再次安装MSSQL:他/她可以安装它，但这将是另一个实例.该实例对您实例中的用户一无所知，因此您应该是安全的.
Regarding you fear that local admin can install MSSQL again: he/she can install it, but it would be another instance. That instance knows nothing about users in your instance so you should be safe.
您还可以考虑使用应用程序角色在SQL Server中.
You can also consider usage of application roles in SQL server.
这篇关于保护LocalDB免受用户访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
