January 11, 2011 at 10:47 am
I have a small problem where when I restart my server my sql service will not start back up. I have done a lot of research and attempted a few things, 1 article said to uncheck the compression checkbox on the temp database (mine is unchecked), another article said to check login name and passwords... Then upon checking my error logs, I find some issues.
My error, pretty standard;
Error: 18456, Severity: 14, State: 5.
My issue;
The login is always changing, and its just random logins... What I mean by this is,
Login failed for user 'r00t'. Reason: Could not find a login matching the name provided.
Login failed for user 'owner'. Reason: Could not find a login matching the name provided.
Login failed for user 'mysql'. Reason: Could not find a login matching the name provided.
Login failed for user 'staff'. Reason: Could not find a login matching the name provided.
Login failed for user 'a'. Reason: Could not find a login matching the name provided
Login failed for user 'b'. Reason: Could not find a login matching the name provided.
Login failed for user 'c'. Reason: Could not find a login matching the name provided.
I have checked everywhere and nowhere can I find these login names, the only error message sql throws at the time of non-start is that the program did not start in a timely fashion. I check the error logs and find TONS and TONS of login errors as listed about. 1 file alone contains 13,128kb almost completely login errors.
In my server manager error logs I find
Error: 7009
A timeout was reached (30000 milliseconds) while waiting for the SQL Server (MSSQLSERVER) service to connect.
Can anyone tell me;
1. Why in the world I am getting so many login errors from random logins?
2. Why my service would not work after a restart, and if I restart my server enough times (say 20) it magically starts working again?
Windows server 2008 r2
SQL server 2008
January 11, 2011 at 10:57 am
My first thought is something is trying to hack your sql machine, you should figure out where it is coming from. The IP address of the machine(s) should be in the log. Have your network admins track it down..
As far as why SQL won't come up, what appears in the log? You didn't really tell us..
CEWII
January 11, 2011 at 10:59 am
im curious how its logging those login attempts if the engine is not running?
if it is running when you get those logins, open a profile trace, and capture "Failed Login Attempts" and you can see where they are coming from....
January 11, 2011 at 10:59 am
You are under a brute force attempt to login, there's a virus or a trojan on your system.
Get a network security expert in NOW.
The login is always changing, and its just random logins... What I mean by this is,
Login failed for user 'r00t'. Reason: Could not find a login matching the name provided.
Login failed for user 'owner'. Reason: Could not find a login matching the name provided.
Login failed for user 'mysql'. Reason: Could not find a login matching the name provided.
Login failed for user 'staff'. Reason: Could not find a login matching the name provided.
Login failed for user 'a'. Reason: Could not find a login matching the name provided
Login failed for user 'b'. Reason: Could not find a login matching the name provided.
Login failed for user 'c'. Reason: Could not find a login matching the name provided.
This is a brute force 'standard names' attack. Get the server offline and isolate it, check the error logs on ALL SQL Servers in your company asap. Find the IP address of the offending machine and locate the reason it's doing this. My guess is someone's laptop got infected, brought it into the LAN, it pinged around finding SQL Servers, and began a zombie attack.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 11, 2011 at 11:18 am
I hope you can find your hacker using...
/*
DBA_FailedConnectionTracker: Maintain an overview of which connections are made to this SQLServer instance
-- This implementation user SQLServer Service Brocker with Event Notifications
*/
USE YourAuditDB;
if object_id('dbo.T_DBA_FailedConnectionTracker') is null
begin
print 'Table [T_DBA_FailedConnectionTracker] Created';
CREATE TABLE [dbo].[T_DBA_FailedConnectionTracker](
[host_name] [varchar](128) NOT NULL,
[program_name] [varchar](128) NOT NULL,
[nt_domain] [varchar](128) NOT NULL,
[nt_user_name] [varchar](128) NOT NULL,
[login_name] [varchar](128) NOT NULL,
[original_login_name] [varchar](128) NOT NULL,
[client_net_address] [varchar](48) NOT NULL,
[Database_Name] [varchar](128) not null,
[tsRegistration] datetime NOT NULL default getdate(),
[FailedLoginData] XML
) ;
Create clustered index clX_DBA_FailedConnectionTracker on [dbo].[T_DBA_FailedConnectionTracker] ([tsRegistration]);
Create index X_DBA_FailedConnectionTracker on [dbo].[T_DBA_FailedConnectionTracker] ([login_name], [program_name]);
end
/*
source using SSB : http://www.simple-talk.com/sql/sql-server-2005/logon-Tracker/
*/
-- Enable Service Broker for YourAuditDB database if it's the case
IF EXISTS( SELECT *
FROM sys.databases
WHERE [name]=N'YourAuditDB'
AND is_broker_enabled = 0 )
Begin
print 'SSB enabled';
ALTER DATABASE YourAuditDB SET ENABLE_BROKER;
END
-- We will access from the activated stored procedure a view that is
-- located in a different database
-- the sys.dm_exec_sessions dynamic management view
-- The security context of the stored procedure would not allow us to do so
-- unless we set the the TRUSTWORTHY option to ON.
-- Why and another method you can find here:
-- http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx
IF EXISTS( SELECT *
FROM sys.databases
WHERE [name]=N'YourAuditDB'
AND is_trustworthy_on = 0 )
Begin
print 'trustworthy switched to ON';
ALTER DATABASE YourAuditDB SET TRUSTWORTHY ON;
END
-- Create a queue
CREATE QUEUE Q_FailedLogon_Tracker_Queue;
-- Create a service
CREATE SERVICE S_FailedLogon_Tracker_Service
ON QUEUE Q_FailedLogon_Tracker_Queue([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
-- Create a route
CREATE ROUTE R_FailedLogon_Tracker_Route
WITH SERVICE_NAME = N'S_FailedLogon_Tracker_Service'
, ADDRESS = N'LOCAL';
go
/* current user get ownership of EVENT NOTIFICATION, so switch to 'sa' */
EXEC AS LOGIN = 'sa';
go
-- Create the event notification at the server level for the AUDIT_LOGIN event
CREATE EVENT NOTIFICATION N_Failed_Login_Notification
ON SERVER FOR AUDIT_LOGIN_FAILED
TO SERVICE 'S_FailedLogon_Tracker_Service', 'current database';
go
/* Switch back to original user */
REVERT;
GO
-- Create the stored procedure that will handle the events
-- First set the options required to work with the XML data type
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.spc_DBA_FailedConnectionTracker
AS
BEGIN
SET NOCOUNT ON;
-- Use an endless loop to receive messages
WHILE (1 = 1)
BEGIN
DECLARE @messageBody VARBINARY(MAX);
DECLARE @messageTypeName NVARCHAR(256);
WAITFOR (
RECEIVE TOP(1)
@messageTypeName = message_type_name,
@messageBody = message_body
FROM Q_FailedLogon_Tracker_Queue
), TIMEOUT 500
-- If there is no message, exit
IF @@ROWCOUNT = 0
BEGIN
BREAK ;
END ;
-- If the message type is EventNotification do the actual work
IF (@messageTypeName = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification')
BEGIN
DECLARE @XML XML,
@host_name varchar(128) ,
@program_name varchar(128) ,
@nt_domain varchar(128) ,
@nt_user_name varchar(128) ,
@login_name varchar(128) ,
@original_login_name varchar(128) ,
@client_net_address varchar(48) ,
@Database_Name varchar(128) ,
@ts_logon datetime,
@SPID VARCHAR(5);
SELECT @XML=CONVERT(XML,@messageBody)
,@host_name = ''
,@program_name = ''
,@nt_domain = ''
,@nt_user_name = ''
,@login_name = ''
,@original_login_name = ''
,@client_net_address =''
,@SPID ='';
-- Get the SPID and the Login name using the value method
SELECT @SPID = @XML.value('(/EVENT_INSTANCE/SPID)[1]', 'VARCHAR(5)')
, @ts_logon = @XML.value('(/EVENT_INSTANCE/StartTime)[1]', 'NVARCHAR(128)')
, @host_name = @XML.value('(/EVENT_INSTANCE/HostName)[1]', 'NVARCHAR(128)')
, @program_name = @XML.value('(/EVENT_INSTANCE/ApplicationName)[1]', 'NVARCHAR(128)')
, @nt_domain = @XML.value('(/EVENT_INSTANCE/NTDomainName)[1]', 'NVARCHAR(128)')
, @nt_user_name = @XML.value('(/EVENT_INSTANCE/NTUserName)[1]', 'NVARCHAR(128)')
, @original_login_name = @XML.value('(/EVENT_INSTANCE/SessionLoginName)[1]', 'NVARCHAR(128)')
, @login_name = @XML.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(128)')
, @Database_Name = @XML.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(128)')
;
INSERT INTO [dbo].[T_DBA_FailedConnectionTracker]
([host_name], [program_name], [nt_domain], [nt_user_name], [login_name], [original_login_name], [client_net_address], [Database_Name], [tsRegistration], [FailedLoginData] )
values ( @host_name, @program_name, @nt_domain, @nt_user_name, @login_name, @original_login_name, ISNULL(@client_net_address,''), ISNULL(@Database_Name,''), @ts_logon, @XML);
/*
Select *
from dbo.T_DBA_FailedConnectionTracker
order by [tsRegistration] desc
*/
END;
END;
END;
GO
if object_id('dbo.spc_DBA_FailedConnectionTracker') is not null
begin
-- Link the stored procedure to the Q_FailedLogon_Tracker_Queue
ALTER QUEUE Q_FailedLogon_Tracker_Queue
WITH STATUS=ON
, ACTIVATION ( STATUS=ON
, PROCEDURE_NAME = dbo.spc_DBA_FailedConnectionTracker
, MAX_QUEUE_READERS = 1
, EXECUTE AS SELF) ;
end
ELSE
BEGIN
RAISERROR ('DBA Message: SSB Queue FailedConnectionTracker NOT Activated !!! ', 1,1 ) WITH log
END
/*
The information posted to the S_FailedLogon_Tracker_Service, about the AUDIT_LOGIN event, has the following structure:
<EVENT_INSTANCE>
<EventType>AUDIT_LOGIN_FAILED</EventType>
<PostTime>2009-02-05T13:21:12.640</PostTime>
<SPID>56</SPID>
<TextData>Login failed for user 'ikke_test'. [CLIENT: <local machine>]</TextData>
<DatabaseID>1</DatabaseID>
<NTUserName />
<NTDomainName />
<HostName>WS20098002</HostName>
<ClientProcessID>5456</ClientProcessID>
<ApplicationName>Microsoft Data Access Components</ApplicationName>
<LoginName>ikke_test</LoginName>
<StartTime>2009-02-05T13:21:12.637</StartTime>
<EventSubClass>1</EventSubClass>
<Success>0</Success>
<ServerName>WS20098002\SQL2005DE</ServerName>
<Error>18456</Error>
<DatabaseName>master</DatabaseName>
<RequestID>0</RequestID>
<EventSequence>16981</EventSequence>
<IsSystem />
<SessionLoginName>ikke_test</SessionLoginName>
</EVENT_INSTANCE>
event_type is obviously AUDIT_LOGIN.
post_time contains the time when the event is posted to the service.
spid represents the ID of the session for the event.
text_data contains a semicolon-delimited list of all set options.
binary_data contains the session level settings, including ANSI nulls, ANSI padding, cursor close on commit, null concatenation, and quoted identifiers.
database_id is the ID of the default database or the ID of the database used by the USE database statement if any.
nt_user_name represents the Windows user name.
nt_domain_name represents the Windows domain to which the user belongs.
host_name contains the name of the computer on which the client is running.
client_process_id is the ID assigned by the host computer to the process where the client application is running.
application_name is the name of the client application.
login_name is the name of the login used.
start_time represents the time that the event started.
event_subclass indicates if the connection is pooled or non-pooled with values 1 for non-pooled and 2 for pooled.
success element indicates if the authentication succeeded and has the values 1 for success and 0 for failure. For the AUDIT_LOGIN event it will always contain value 1.
integer_data represents the network packet size.
server_name represents the name of the instance of SQL Server on which the event occurred.
database_name is the name of the database
login_sid contains the security identification number (SID) of the logged-in user.
request_id represents the ID of the request.
event_sequence represents the sequence of the event within the request.
is_system indicates if the event occurred for a user or a system process. In this case it would always be a user process.
*/
/*
USE YourAuditDB;
GO
-- clean up only if needed
/* In case of Problems first drop the EVENT NOTIFICATION subscription */
--DROP EVENT NOTIFICATION N_Failed_Login_Notification ON SERVER
--ALTER QUEUE Q_FailedLogon_Tracker_Queue WITH STATUS=OFF
--DROP EVENT NOTIFICATION N_Failed_Login_Notification ON SERVER
--DROP ROUTE R_FailedLogon_Tracker_Route
--DROP SERVICE S_FailedLogon_Tracker_Service
--DROP QUEUE Q_FailedLogon_Tracker_Queue;
--DROP PROCEDURE dbo.spc_DBA_FailedConnectionTracker
--
*/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 11, 2011 at 5:46 pm
All of the ip's listed in my error list are from china...
CLIENT: 221.146.78.14
CLIENT: 24.159.19.95 (most of them are from here)
CLIENT: 60.191.232.159
CLIENT: 60.191.232.231
Tried running the SQL Server Profiler but it seems as though its "broken" I just get an error stating my tracing tool has stopped working. This is the error from that;
Description:
Stopped working
Problem signature:
Problem Event Name:APPCRASH
Application Name:PROFILER.EXE
Application Version:2007.100.1600.22
Application Timestamp:48753de7
Fault Module Name:KERNELBASE.dll
Fault Module Version:6.1.7600.16385
Fault Module Timestamp:4a5bdbdf
Exception Code:e0434f4d
Exception Offset:0000b727
OS Version:6.1.7600.2.0.0.272.7
Locale ID:1033
So you guys are thinking either virus or hacker?
My SQL server is still running because if I restart my server enough times it starts working again. I think for now if you guys can help me stop the attacks, I will attempt to solve the service issue out next. Seems as though I have a few things hitting me at once.
January 12, 2011 at 12:41 am
Can't you block these addresses in your firewall, so they don't even get to your sqlserver ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 12, 2011 at 1:50 am
Does your sqlserver have to be exposed to the internet ?
You could also change the port number it listens on
January 12, 2011 at 7:00 am
I would love to make it not accessible to the internet. I do however have websites that use it, could you please link me to good article that would explain this to me.
As far as blocking ip's at the firewall, I started doing that last night, I haven't checked my logs yet to see if they are still coming in, but I would assume that whoever is doing it will just roll their ip and hit it again.
January 12, 2011 at 7:05 am
It would seem safer to me to whitelist your known ip addresses at the firewall rather than blacklist those 'bad' ones.
January 12, 2011 at 8:05 am
How could I whitelist them if they are connecting anonymously due to website data?
January 12, 2011 at 8:12 am
You need to whitelist your known good ipaddress on your firewall , this is not a sqlserver issue.
But , this is probably not the best way to handle this .
The sqlserver should not be exposed to the internet.
Maybe a VPN would be a better option ,
Im ducking out of this one now , im not a security expert.
January 12, 2011 at 8:22 am
I tend to agree that you don't want your SQL Server exposed directly. Can you explain a little more on your setup?
January 12, 2011 at 8:44 am
Sure,
Windows server 2008 r2
Microsoft SQL server 2008
4 websites
Same connection string on all sites;
<add name="XXXXXX" connectionString="Data Source=.\SQLExpress\instance,1433;integrated Security=SSPI;Initial Catalog=XXXXX" />
Just looking at the string out of the webconfig I see it connects to express instance, so that should be changed...
January 12, 2011 at 8:46 am
Apologies. I was unclear.
That's good to know, but more about what connects to what, and for what purpose? How does this application work?
Are you thinking this is a series of connection attempts through the web sites to the SQL Server?
The way you typically set things up is that the web server connects to the SQL Server, and only the web server can. So someone from the Internet cannot connect directly to your SQL Server. Even is this is one box, you should only allow connections to SQL Server from the web server IP, not all IPs.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply