| Author |
Topic |
|
CanadaDBA
583 Posts |
 Posted - 2007-10-05 : 15:08:01
|
| I have a database with sensitive data. Users must query through an application and no one but me and my boss should be able to access directly to the database and query. What should I do?SQL Authentication is what the application uses to connect. Canada DBA |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-05 : 15:11:58
|
| If your users only have exec on stored procedures, then this is easy. So what access do the users have?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
CanadaDBA
583 Posts |
Posted - 2007-10-05 : 15:52:32
|
| The users go through the one login and have read/write to all tables. Stored procedures should be exec only, and I really don't think there are any. I wrote one myself just for converting TT time fields... but I don't recall any other official ones.Canada DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-05 : 15:56:00
|
| If the users have read/write, then there's no way to stop them from querying the database directly. Even if you don't give them the SQL client tools, they can always download them and install it.This is another reason why you should always use stored procedure with no dynamic SQL for all data access.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
CanadaDBA
583 Posts |
Posted - 2007-10-05 : 15:58:46
|
| But the application connects and the users know a user id and password for the application not the SQL Server. And SQL Authentication is what the application uses to connect. Canada DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-05 : 16:00:12
|
| Then perhaps you should have given us this information in your first post. If the users do not a userid and password to get into the database, then of course they won't even be able to connect.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
CanadaDBA
583 Posts |
Posted - 2007-10-05 : 16:09:47
|
| Sorry. I should have put right information or more information in first post.None of the users have any user id or password. They use their network id to open the application and the application has its own SQL Server user id and connects to the database.Now the question is that what about those admin users who have access rights to the machine (like Network admin). How can I restrict all who have access to the machine.And the second question is that how can I secure my backups?Canada DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-05 : 16:12:05
|
| 1. Remove the sysadmin role from the BUILTIN\Administrators group2. You'd probably have to put the files on a server where only you and your boss have access. That means backing up over the network, which isn't ideal for performance reasons.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
CanadaDBA
583 Posts |
Posted - 2007-10-05 : 16:20:15
|
| 1. Is removing sysadmin enough to prevent anybody "on" that machine to see the data? 2. Isn't there anyway to encrypt the backup? I mean without using third party.Canada DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-05 : 16:55:59
|
| 1. You'd have to review what other access you have in place.2. BOL is always helpful for this type of thing.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-06 : 00:02:16
|
| Take look at application role. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-06 : 05:17:02
|
| You might want to also move the Port on that server to something greater than 10,000 - that will stop [most] port scanners knowing that SQL Server exists on that machine ... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-06 : 05:17:34
|
| ... and adds one more thing a hacker has to try.(Sorry, pressed SUBMIT too quick) |
|
|
|
CanadaDBA
583 Posts |
Posted - 2007-10-10 : 10:16:31
|
| Good idea Kristen. Thanks!Canada DBA |
|
|
|
CanadaDBA
583 Posts |
Posted - 2007-10-10 : 12:20:53
|
| Does anybody have a link about available ports for SQL Server? I mean can I just set a number for the port or there is a list to select?Canada DBA |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-10-10 : 12:48:24
|
http://www.iana.org/assignments/port-numbers
Future guru in the making. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-10 : 13:36:21
|
| "I mean can I just set a number for the port or there is a list to select?"Above 10,000 .. then you are probably find to just pick one.Especially if its a dedicated SQL box!Kristen |
|
|
|
CanadaDBA
583 Posts |
Posted - 2007-10-12 : 14:05:22
|
| Above 10,000 .. then you are probably find to just pick one.Especially if its a dedicated SQL box!Is this a suggested thing for all my servers? Have you set yours this way?Canada DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-12 : 14:08:53
|
| It's suggested for all SQL Servers that are Internet-facing. The SQL Server isn't usually on the Internet directly, but rather behind a firewall. It's the application and/or web server that is exposed to the Internet. So the firewall/network team will close off all ports except the one(s) you choose.We leave the port at 1433 for all internal-facing systems, but 10000+ for all external-facing ones.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
CanadaDBA
583 Posts |
Posted - 2007-10-12 : 15:21:29
|
| Thanks Tara! That was what I was looking for. :)Canada DBA |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 18:04:06
|
"Have you set yours this way?"AbsoBloodyLutely. Until we did we have frequent port scan attacks. Even on machines which supposedly were tightly configured to only be visible to a couple of application servers. (Human nature and The Real World got in the way somewhere along the line!)"Don't leave home without it" would be my advice."We leave the port at 1433 for all internal-facing systems"FWIW we don't even install internal's on 1433 any more. Some twit consultant will pitch up, install his laptop on the LAN together which his Bluetooh-Phone-internet-access-thingie and something will start port-scanning within moments. If I wasn;t a consultant I won;d have a good word to say about them ...  Kristen |
|
|
|
Next Page
|
Restrict direct DB access