| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2002-12-18 : 07:56:19
|
| The only access I need to our SQL 2000 databases is from our web and myself.Is there a way to lock down access to SQL based on IP address??Sam |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-12-18 : 08:09:13
|
| You can install a firewall on the SQL Server and block all ports and IP addresses except the one(s) through which you want to connect. There is also some built-in IP security in Windows 2000 that might suffice. Other than these methods there is no built-in SQL Server feature to restrict access in the way you want. |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-12-18 : 08:59:48
|
Thanks. I was looking into the firewall option this morning, and I've made a related post in the development forum.So Rob.. what kind of security do your databases have?   Sam(Maybe a good article on this subject).. |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2002-12-18 : 09:18:37
|
| Another nice thing is to change the port number to which the server listens, to something else than 1433. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-12-18 : 09:29:36
|
| The most common security threat, once you've implemented a strong passwords, is SQL Injection.Jay White{0} |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2002-12-18 : 10:17:10
|
| Solutions to SQL injection:-Use only procedures and limit permissions-Run a replace on all strings from the user. Replace ' with '' |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-12-18 : 13:58:34
|
| If the port number is changed, how does it affect my use of Enterprise manager and Query Analyzer??Sam |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-12-18 : 14:01:34
|
| You'd use the Client Network Utility to create an alias for the server, and set the port there. You then connect to the alias. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-12-18 : 14:15:22
|
| You don't have to create an alias to do it, but it is the easiest way if you are using multiple ports. In our environment, our production SQL Servers all listen on the same port, but not on 1433. And since we are unable to get to the other SQL Servers (test, QA, etc.), so this makes sense for us. So instead of creating an alias for each server, you can just change the default port in Client Network Utility to the one that they are listening on. To do this, just go to Client Network Utility (this will default to the general tab), click on TCP/IP, then click on properties, now change the port to whatever is appropriate. |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-12-18 : 17:44:12
|
| Instance discovery is done via UDP 1434 thus as long as that is available internally any client with MDAC 2.6 and higher can dynamically determine the port that SQL is listening on. You can use IPSEC on windows 2000 to apply filters and rules as to what IP addresses can access the server. Have a look at [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetHT18.asp[/url]The most secure thing you can do is use only windows authentication because unless you are using IPSEC or SSL then all communication to and from SQL is wide open and in clear text or incredibly simple encryption. Admittedly this is usually only a concern internally assuming basic firewall precautions are in place but even so, it often shocks people how easy it is to decrypt SQL authentication passwords.HTHJasper Smith |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-12-19 : 07:48:40
|
| For a nice firewall on a budget, get yourself an old P90 or higher and put IPCOP on it. IPCOP is a stripped back Linux distro designed as a firewall / router with a nice, easy to configure web interface.Basically the same insides as a Watchguard box, but with a price tag a thousands of $$$ less.Damian |
|
|
|
|
Increasing server security