Passively detect attempts to guess passwords

This scripts looks for possible malicious attempts to hack your SQL Server instance. This is done by reading the current error log and searching for multiple failed login attempts from the same IP address. Please note that this requires that “Login auditing for failed logins” is enabled in the properties of your SQL Server instance.

To run this script, simply copy and paste the script into a connected query tab in Microsoft SQL Management Studio and then execute it. It will output a summary list of all failed attempts to guess passwords within the last 24 hours on your SQL Server instance.

begin

create table #errorLog (
LogDate datetime,
ProcessInfo varchar(250),
[Text] varchar(8000)
)


--read the current error log
insert into #errorLog (LogDate, ProcessInfo, [Text])
exec sp_readerrorlog 0, 1 --0 = current(0), 1 = error log


--find brute force attempts to guess a password
select
replace(right([Text],charindex(' ', reverse([Text]))-1), ']', '') as IP,
substring([Text], charindex('''', [Text]) + 1,  charindex('.', [Text]) - charindex('''', [Text]) - 2  ) as [User],
count(LogDate) as [Number of login attempts],
min(LogDate) as [Attack started],
max(LogDate) as [Attack ended],
datediff(minute, min(LogDate), max(LogDate)) as [Attack duration in minutes],
cast(cast(count(LogDate) as decimal(18,2))/isnull(nullif(datediff(minute, min(LogDate), max(LogDate)),0),1) as decimal(18,2)) as [Attack intensity - Login attempts per minute]

from #errorLog

where
--limit data to unsuccessful login attempts in the last 24 hours
ProcessInfo = 'Logon'
and [Text] like 'Login failed for user%'
and datediff(hour, LogDate, getdate()) <= 24

group by
[Text]

having
count(LogDate) > 3 --filter out users just typing their passwords incorrectly

order by
[Number of login attempts] desc,
[Attack ended] desc



--clean up temp tables created
drop table #errorLog


end

What's changed recently and List of Stored Procedures modified in past 7 days

SELECT TOP 22 * FROM sys.objects ORDER BY modify_date DESC


SELECT * FROM sys.objects
WHERE TYPE = 'P' AND DATEDIFF(D,modify_date, GETDATE()) < 7

To remove the specified Characters in the Given String

Alphabetic only: SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z')
Numeric only: SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^0-9+-/')
Alphanumeric only: SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z0-9')
Non-alphanumeric: SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', 'a-z0-9')

CREATE FUNCTION GEN_FN_StripCharacters

(

    @strInputString NVARCHAR(MAX), 

    @strMatchExpression VARCHAR(255)

)

/*

---Created By : Ram    

--Date : 15-Feb-2013

--- Purpose : To remove the specified Characters in the Given String

Alphabetic only: SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z')

Numeric only: SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^0-9+-/')

Alphanumeric only: SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z0-9')

Non-alphanumeric: SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', 'a-z0-9')

*/

RETURNS NVARCHAR(MAX)

AS

BEGIN

    SET @strMatchExpression =  '%['+@strMatchExpression+']%'

    WHILE PatIndex(@strMatchExpression, @strInputString) > 0

        SET @strInputString = Stuff(@strInputString, PatIndex(@strMatchExpression, @strInputString), 1, '')

    RETURN @strInputString

END

http://www.sqlservercentral.com/scripts/Remove+character+or+number+from+String/99755/

Query for comma-separated ids to comma-separated values

http://stackoverflow.com/questions/14612394/query-for-comma-separated-ids-to-comma-separated-values

use interview

DECLARE @Departments TABLE
(
  ID INT PRIMARY KEY,
  Dept VARCHAR(32) NOT NULL UNIQUE
);

DECLARE @Employees TABLE
(
  ID INT PRIMARY KEY,
  Name NVARCHAR(64) NOT NULL,
  Depts VARCHAR(255) NOT NULL
);

INSERT @Departments VALUES
  (1,'HR'),  (2,'Accts'),  (3,'IT');

INSERT @Employees VALUES
  (1,'Kevin','2,1'), (2,'Michelle','1'),
  (3,'Troy','1,3'),  (4,'Rheesa','2,3,1');

SELECT ID, Name, Depts = STUFF((SELECT ',' + d.Dept
    FROM @Departments AS d
    INNER JOIN @Employees AS ei
    ON ',' + ei.Depts + ',' LIKE '%,' + CONVERT(VARCHAR(12), d.id) + ',%'
    WHERE ei.ID = e.ID
    ORDER BY Dept
    FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
FROM @Employees AS e
ORDER BY ID;

SOAP Interface

http://msdn.microsoft.com/en-us/library/ff512390.aspx

Using the SOAP Interface

The SOAP interface supports client application scenarios and a rich .NET service client programming model. Developers can use their development technology of choice with this interface.

To start using the SOAP interface for the Microsoft Translator service you need SOAP web service reference
To add reference to SOAP web service, right-click on the Visual Studio 2010 project and click on Add Service Reference... Then provide address as http://api.microsofttranslator.com/V2/Soap.svc and click Go. Once service information is downloaded, provide Namespace as "TranslatorService" and click Ok.

This will generate the proxy code in a LanguageServiceClient class in the project. In order to call the SOAP web service in the C# code, an instance of the LanguageServiceClient class is required. This instance is created using a static constructor

C#

                                
TranslatorService.LanguageServiceClient client = new TranslatorService.LanguageServiceClient();
                            

Authorization Header

To make request to any method of Microsoft translator you will need access token. Process of obtaining access token is explained in detailed at Obtain Access Token. Use the value of access token in the Authorization header of the subsequent calls to the Microsoft Translator API. This Token is valid for 10 minutes.

Authorization header value should be in following format. (Note: One BLANK space between Bearer and Access Token Value)

"Bearer" + " " + Access Token Value

To send this authorization header as a part of SOAP request, define a variable of type HttpRequestMessageProperty.

C#

                                
HttpRequestMessageProperty httpRequestProperty = new HttpRequestMessageProperty();
                            

Now to call Translate method, we have to provide all required parameters. appId is required parameter but as we are using Authorization header leave appId parameter blank.
Code block below ensures that Authorization header added to the SOAP request.

C#

                                
// Creates a block within which an OperationContext object is in scope.
using (OperationContextScope scope = new OperationContextScope(client.InnerChannel))
{
    OperationContext.Current.OutgoingMessageProperties[HttpRequestMessageProperty.Name] = httpRequestProperty;
    string sourceText = "Use pixels to express measurements for padding and margins.";
    string translationResult;
    //Keep appId parameter blank as we are sending access token in authorization header.
    translationResult = client.Translate("", sourceText, "en", "de", "text/plain", "");
    Console.WriteLine("Translation for source {0} from {1} to {2} is", sourceText,"en","de");
    Console.WriteLine(translationResult);
}

Sp create For all DB

alter proc SPcreater(@strs nvarchar(max)
)
as begin

DECLARE @ID  varchar(50)
DECLARE c CURSOR READ_ONLY FAST_FORWARD FOR
    SELECT name
    FROM master..sysdatabases where name like 'iclaim%' and name !='iclaim'
-- Open the cursor
OPEN c

FETCH NEXT FROM c INTO @id
WHILE (@@FETCH_STATUS = 0)
BEGIN
 
DECLARE @command varchar(max)
SELECT @command = 'USE '+ @id +''
SET @command=@command+'

Go'

 Set @command =@command +@strs

SET @command=@command+'
 
  GO      
   '
print @command
    FETCH NEXT FROM c INTO @id
END

-- Close and deallocate the cursor
CLOSE c
DEALLOCATE c

end

restrict download

The null coalescing operator: ??

This is a new feature of c# 2.0. The null coalescing operator is a short cut for checking if a value is null and if so returning the value of the second operand. Kind of like an IIF. The syntax is as follows

string newValue = someValue ?? "default";

The first operand someValue must be a nullable type. The above code will set the value of newValue to someValueunless it's null, then it'll set it to "default".
You could also use this inline:

Console.WriteLine("The value is " + (someValue ?? "null"));

another use
return (bool)(ViewState["IsPaged"] ?? true);

If you really want to use an identifier that clashes with a keyword, you can qualify it with the @ prefix. For instance:

 class class  {...}    // illegal
 class @class {...}    // legal
The @ symbol doesn't form part of the identifier itself, so @myVariable is the same as myVariable. check out this example
using System;
using System.Collections.Generic;
using System.Text;

namespace ConsoleApplication7
{
   class Program
   {
       static void Main(string[] args)
       {
           int @int = 10;
           double @double = 10.121;
           float @float = (float)(@int + @double);
           Console.WriteLine(@float.ToString());
           Console.Read();

       }
   }
}


How to restrict download of specified file types in asp.net

In this post i will show you how to restrict your web app so that .txt (whatever file extensions you want ) files can not be downloaded. Open your web.config file and register following setting in your web.config

<system.web>
   <httpHandlers>
       <add verb="*" path="*.txt" type="System.Web.HttpForbiddenHandler" />
   </httpHandlers>
</system.web>
For more details how to register handler in web.config check out this link