The database of the secure SQL server is copied

存储架构 2017-12-02

I'm new to SQL server and I have a question

I created a database on a SQL server local DB instance and then I copied the .mdf file of the database to a USB flash drive and I was able to open the database from a different user account' So I want to understand why is it so? Does it mean that anyone can copy my database file and open it on their own server? And how can I secure that?


Does it mean that anyone can copy my database file and open it on their own server?

Yes, as long as it's the same version of SQL Server. This is why it's critical to maintain control of a server running SQL Server and prevent unauthorized access to the file system or backups.

So I want to understand why is it so?

The same software uses the same format. Much like how an Excel document can be saved on one computer and opened on another, so, too, can a database. By default, SQL Server stores data in the easiest and most straightforward manner it can for best possible performance. Security of the data files is often a secondary concern as most SQL Servers physically reside behind locked doors and OS access is restricted to computer administrators with domain authentication.

If you're running SQL Server 2008+ Enterprise, you have access to Transparent Data Encryption
which will encrypt the data files on disk. That doesn't prevent moving the database
if you have full access to the original host system, but it does mean there are additional steps and it will be difficult to access the data by directly reading the data from the disk. Furthermore, you can also encrypt your backups
(I believe SQL Server Standard and above supports this, I'm not sure about Express).

As far as Express LocalDB... your options are pretty limited. You can use NTFS level encryption, but that will impact performance as the DB engine is no longer aware that encryption is going on. Furthermore, anybody who can access the DB unencrypted would be able to access the DB files to copy, so you're not actually protected against inside attacks. You could also use BitLocker full disk encryption, but again, that will come with some I/O cost and, again, doesn't protect against inside attacks.

Finally, no matter what you do anybody with sa
or dbo
level access to the server will be able to read your data. You can choose to store only encrypted data in the DB, but that pretty much eliminates any advantage of using SQL, and your application would still need to store the means to decrypt it somewhere, which means someone could find your decryption key and work backwards from there.


SQL Saturday Houston is this weekend SQL Saturday Houston is this weekend Daniel Janik Posted on 19 June 2018 Comments I’m speaking along with many oth...
From SQL to AWS Kinesis, EMR and Elasticsearch That thing that happens when your company is scaling so fast you have to replace your infrastructure within weeks or the whole thing crashes. This is ...
SQL Server 2017 Administration Inside Out SQL Server 2017 Administration Inside Out Posted on 15 November 2017 Comments For the last five months or so, I have been ...
记录一则enq: TX – row lock contention的分析过程... 故障描述:与客户沟通,初步确认故障范围大概是在上午的8:30-10:30之间,反应故障现象是Tomcat的连接数满导致应用无法连接,数据库alert中无明显报错,需要协助排查原因。 1.导入包含故障时刻的数据 2.创建m_ash表,明确故障时刻 3.确定异常时刻...
记一次SQL调优/优化(SQL tuning)——性能大幅提升千倍以上... 好久不写东西了,一直忙于各种杂事儿,恰巧昨天有个用户研发问到我一个SQL调优的问题,说性能太差,希望我能给调优下,最近有些懒,可能和最近太忙有关系,本来打算问问现在的情况,如果差不多就不调了,那哥们儿说:现在要半个小时才出结果。这个确实有点离谱,这么慢的性能,再有耐心的人也等不及,没办法,只能开工了...