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 2005 Forums
 SQL Server Administration (2005)
 Security issue

Author  Topic 

Abhi.karnawat
Starting Member

7 Posts
Posted - 2010-01-27 : 05:50:10
hey,

ive been looking at my management log for last 2 days now and i see one IP address trying to log on sa account and fail giving error msg 18456 Severity 14 State 7

i am afraid someone is trying to hack the sql server i have denied login of the sa account but i cant seem to get rid of the ppl trying to gain access..

is there a way that if there are multiple login attempts in very short time that the sql server just drops the connection with that IP or is there way i can hide my sql server from these type of attacks.

kind regards
abhi

Kristen
Test

22859 Posts
Posted - 2010-01-27 : 05:56:24
We have our SQL Servers on a high port number (above 10,000) and the scum with port scanners don't seem to bother searching that range.

Even if your server SA password is secure the server has to waste CPU denying the login attempt ... (which a high port number solves).

Even in secure, internal, environment someone may plug in a laptop with SQL virus that starts trying to find SQL Servers on the default port ... so worth moving the port to non-standard value I think.

Connection string needs to change to append ",10000" (i.e. the port number you assign) to the end of the server name
Go to Top of Page

Abhi.karnawat
Starting Member

7 Posts
Posted - 2010-01-27 : 06:09:58
hey Kristen thx for the prompt reply,
do u mean i need to change port 1433 to 10,000 or above?
if so how do i go about doing this?
rgards,
abhi
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-01-27 : 07:16:01
"do u mean i need to change port 1433 to 10,000 or above?"

Yup, that's exactly it. Beware that when you do that all connections will fail - until you modify the connection string used by each application that needs to connect.

Programs : SQL server : Configuration : Configuration Manager

SQL Server 2005 Network configuration : Protcols for MSSQLSERVER

Right click TCP/IP and choose Properties

Switch to [IP Addresses] tab

then you can change the PORT NO.

You would be hard pressed to guess that was where it was, eh?
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts
Posted - 2010-01-27 : 08:13:21
is this IP Address inside your network? If so, how often are they trying? Point is, if it is from within your network, it may be an application that was written to use sa at one point. should be ez to find.

if it is an external ip, then i'll add to Kristen's comments by saying that the SQL Server shouldn't be facing the internet in most deployments.
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts
Posted - 2010-01-27 : 08:13:21
is this IP Address inside your network? If so, how often are they trying? Point is, if it is from within your network, it may be an application that was written to use sa at one point. should be ez to find.

if it is an external ip, then i'll add to Kristen's comments by saying that the SQL Server shouldn't be facing the internet in most deployments.
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts
Posted - 2010-01-27 : 08:14:22
quote:
Originally posted by Kristen

We have our SQL Servers on a high port number (above 10,000) and the scum with port scanners don't seem to bother searching that range.


Dang, now I have to start scanning above 10,000 lol
Go to Top of Page

Abhi.karnawat
Starting Member

7 Posts
Posted - 2010-01-27 : 08:25:59
hey,
i changed the port to 15000, but after doing that i could not use the sql server management studio as it wont let me log in.
and my odbc connection fails to connect as well even after changing the port to 15000..
what should i do or what am i doing wrong?

i changed all the ports to 15000 where ever it was 1433 in the TC/IP properties..

thx
Go to Top of Page

Abhi.karnawat
Starting Member

7 Posts
Posted - 2010-01-27 : 08:28:20
to russel:
IP address is coming from outside the network, form china somewhere in shanghai..
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-01-27 : 08:51:50
I can't guarantee that 15000 is a good choice. I can't tell you what we use (sorry!) but its less than that. Maybe try 10000 for starters. We did try to use 14330 (because we thought that would be easy to remember!) but it didn't work - hence maybe 15,000, which is higher, won't work either.

You need to change the Client for SQL Tools to be able to connect.

Back in:

Programs : SQL server : Configuration : Configuration Manager

SQL Native Client Configuration

you have two choices:

In Client Prtocols you could Right Click TCP/IP and change the default port - that would be for ALL connections made, which is probably not what you want

or

In Aliases set up (or edit if already exists) an Alias for the server

Alias Name - Short Nickname

Port No - e.g. 10000

Protocol = TCP/IP

Server = Server Name or IP address (i.e. something that you can PING and get a response)


If you already had an Alias for your server in the list then it will resume working after you change the port. Dunno if it is local - perhaps you can add "local" or "localhost" as the Server - or 127.0.0.1
Go to Top of Page

Abhi.karnawat
Starting Member

7 Posts
Posted - 2010-01-27 : 09:18:58
ok i did that and got it working on a port gr8er than 10000,
but now the odbc i used to connect doesnt connect to the server anymore even after changing the connection port in the odbc connection configuration..

whats next?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-01-27 : 11:32:02
Dunno about ODBC, I've only ever used connection strings. Do you have anything like that with ODBC (i.e. how you connect to ODBC in the application?) I guess not as the whole point was that all the config was in the ODBC definition ...

There are some lightweight test tools for ODBC - just choose the ODBC connection and type in a Query. That would prove whether the ODBC config is working correctly, or not, or whether there is something extra the App needs to do in connecting to the ODBC data source.
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts
Posted - 2010-01-27 : 12:44:00
http://dba.fyicenter.com/faq/sql_server_2/Configuring_ODBC_DSN_with_Different_Port_Numbers.html
Go to Top of Page

tripodal
Constraint Violating Yak Guru

259 Posts
Posted - 2010-01-27 : 16:58:57
im fairly certain the odbc will test its own connection when you get to the last step.

Please remember that if this is indeed an internet facing SQL server, you will need to allow whatever port you choose through your firewall.

Please tell me you have a firewall.

Also consider, if your sql server really NEEDS to be internet accessable, implement VPN port knocking, or point to point links to keep it from the internet malcontents.

I have vast regions of china pakastan and north korea blocked in my firewall.
Go to Top of Page

Abhi.karnawat
Starting Member

7 Posts
Posted - 2010-01-28 : 01:41:40
Hey,
TO:Kristen.. thx for the help i think i just need to allow the port on the firewall..
To:Russel.. thx for the link.. but i had tried that already
To:Tripodal:
The server is connected to the internet, i have a firewall, and i think i need to change to port on that and try it again..
Now i have showrooms that record sales and customers through MS Access front end and the Back End is the SQL server to which they connect from the internet.. so i think it needs to be internet accessible.. unless there is another way, and i'm more than will to try it if it would resolve the issue, i do not know about VPN port knocking or point to point links..
And how do i go abut blocking these regions from the firewall?

thx again you'all
abhi
Go to Top of Page

Abhi.karnawat
Starting Member

7 Posts
Posted - 2010-01-28 : 02:15:30
hey
tried changing port on firewall and restarting all services on different port server works fine but i cant seem to connect to it through odbc after changing the port of connection in the configuration.. this is a problem as the showrooms need to connect to enter sales and customer data..

i'm running low on option here.. i've still been keeping everything running on the default port and keep getting attacked by different IP's
help??
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-01-28 : 03:02:05
"im fairly certain the odbc will test its own connection when you get to the last step."

Good point, I remember seeing that "Test" step now you mention it

Are you seeing that too Abhi?
Go to Top of Page

tripodal
Constraint Violating Yak Guru

259 Posts
Posted - 2010-01-29 : 15:02:54
The users of your application are connecting from the interent, or are they in the same building as your server?

You can install a second network card.

ALternatively you can create a list of allowed IP's in your firewall. and block everything else to port 1433. What type of firewall are you using?
Go to Top of Page

tripodal
Constraint Violating Yak Guru

259 Posts
Posted - 2010-01-29 : 15:03:29
quote:
Originally posted by Kristen

"im fairly certain the odbc will test its own connection when you get to the last step."

Good point, I remember seeing that "Test" step now you mention it

Are you seeing that too Abhi?


Im good at GUI's. Not alwasy useful tho.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site