Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Table-level password

Author  Topic 

WindChaser
Posting Yak Master

225 Posts

Posted - 2012-03-20 : 20:20:45
Hi folks,

Is there any way to password-lock a table?

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-20 : 20:21:47
Not in the way that you'd like. Encrypt the data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

WindChaser
Posting Yak Master

225 Posts

Posted - 2012-03-20 : 21:14:37
Thanks Tara. That's what I'm doing right now. I was just kind'a hoping for an answer such as "SQL Server 2012 has something like that coming out!". Oh well, I'll just have to go to sleep dreaming of a better future...
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-21 : 04:33:00
Or set up logins so that only the allowed logins can access the table

--
Gail Shaw
SQL Server MVP
Go to Top of Page

WindChaser
Posting Yak Master

225 Posts

Posted - 2012-03-21 : 09:26:53
That's where it gets confusing for me because of my lack of in-depth knowledge. After the installation of an instance, I always perform the following:

1. Change the SA password.
2. Create my login.
3. Drop Login [BUILTIN\Administrators]
4. Drop Login [BUILTIN\Users]
5. Drop Login [strStationName\SQLServer2008MSSQLUser$strStationName$MyInstanceName]

So, I figure that nobody other than I should be able to log in because this should block any Windows Authentication login attempt and I control all SQL Server Authentication logins. Is this rock-solid or have I left other doors open? Also, can the login passwords be hacked?

Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-21 : 09:44:02
Are you using Windows Auth or SQL Server?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

WindChaser
Posting Yak Master

225 Posts

Posted - 2012-03-21 : 09:47:19
I only use my login which uses SQL Server Authentication.
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-21 : 11:33:56
Rather use windows authentication, it is more secure, and either set SQL to windows authent only or completely disable the SA account.

That said, unless you give someone access to the SQL Server and to the database and to the table, they're not going to be able to access it. SQL's passwords are at a login level, then you set the permissions to the databases and objects within, the passwords aren't at a database or object level.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

WindChaser
Posting Yak Master

225 Posts

Posted - 2012-03-21 : 12:41:11
Thanks Gail.

My problem is that I do not have access to the target computers of the clients on which the SQL Server Express is installed. I therefore cannot create a Windows user account on those target computers. Given that circumstance, I've blocked any Windows Authentication login attempt and I control all SQL Server Authentication logins, being SA and mine. I could disable the SA but I've always kept it in case anything should ever happen (corruption?) to my own login. Could a local user find a way around this in order to access the data?
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-21 : 13:12:21
so much for client server...how do you control all of those remote express installs?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

WindChaser
Posting Yak Master

225 Posts

Posted - 2012-03-21 : 13:34:13
Brett, not sure what you meant by "control". The end-users purchase the product and install it on their computers using an InstallShield installer developed here which installs and configures the SQL Server Express. The end-users must not be able to access the data engine directly, which is why we control the logins. Access on our part, if required, is done through a Remote Desktop connection or something similar. Not sure if this answers your question...
Go to Top of Page

WindChaser
Posting Yak Master

225 Posts

Posted - 2012-03-21 : 16:59:54
So, bottom line I guess is: can a SQL Server Authentication password be cracked? Probably a stupid question, otherwise why would the need for field-level encryption exist....?
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-21 : 17:09:32
Anything can be cracked given enough time. That said, if SQL Express is installed on a client machine you cannot secure it from someone who is local admin on the box. Admins have full permissions to everything on the server, all you can do is slow them down.

That's why if security is of interest to you, you host the DB on a server you control. If it's on a user's machine, it is completely out of your control.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-21 : 17:13:12
Encryption is not for the cases where the passwords are cracked, because encryption depends on SQL built in security and if the passwords are cracked the hacker mayhave permission to decrypt. It's for when your have users connecting to the DB with read permissions on a table and they must not be able to see a particular column unless they have other permissions. There are other reasons.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-03-21 : 17:27:52
quote:
Originally posted by WindChaser

The end-users purchase the product and install it on their computers using an InstallShield installer developed here which installs and configures the SQL Server Express. The end-users must not be able to access the data engine directly, which is why we control the logins.


This is a common request and often a misguided one. Do employ the usual security measures, but don't believe that the data that you're selling to folks along with the application is THAT sensitive. If it is, you've made a serious design error.
Go to Top of Page

WindChaser
Posting Yak Master

225 Posts

Posted - 2012-03-21 : 19:48:24
quote:
Encryption is not for the cases where the passwords are cracked, because encryption depends on SQL built in security and if the passwords are cracked the hacker mayhave permission to decrypt.


I encrypt the data using EncryptByPassPhrase and DecryptByPassphrase. The passphrase is embedded in a compiled executable and is not stored in any fashion on SQL Server. So a hacker would have to crack the login password then start cracking the encryption phrase.


quote:
This is a common request and often a misguided one. Do employ the usual security measures, but don't believe that the data that you're selling to folks along with the application is THAT sensitive.


Actually, it's not the data that we're selling but the data that the end-users are accumulating. Our clients are in medical and gather tons of data, much of which is confidential.

quote:
That's why if security is of interest to you, you host the DB on a server you control.


Not every application can be centralized. Many of our clients are small medical clinics with small budgets. A centralized cloud application would entail having TCP/IP redundancies, which is an expensive proposal. One of our competitors offering such an application went bankrupt. Now, that's what I call "a serious design error". Big and centralized offers a lot of benefits, but doesn't fit all business models.

Point is, the objective is to safeguard the data to make it next to impossible to get at, given the limitations of the environment we have to contend with. This said, are there any tools that you can buy straight off the market to crack a SQL Server login password or decrypt data? Can you easily hire a SQL Server wiz off the net to crack data? Does one have to be a rogue Microsoft SQL Server Development Team engineer to know how to hack a SQL Server? These are the questions which are really on my mind.
Go to Top of Page
   

- Advertisement -