Cannot set a credential for principal ’sa’. (Microsoft SQL Server, Error: 15535)

For anyone on SQL Server 2005 who gets this Cannot set a credential for principal ‘sa’ error, the fix is easy, straight forward and simple…

Make sure “Map to Credential” is checked. That’s it. All you have to do is check “Map to Credential” and you’re set. For those without eyes, it’s on the bottom of the first screen where you set the user password (by right clicking the user and selecting “Properties”)

This one had me scratching my head a bit, clean install from Visual Studio 2008 (With Express 2005 Service Pack 2) and SQL 2008 SSMS

How to find + stop SQL injection attacks

There’s a lot of stuff out there about SQL injection attacks, but there’s not much that will help you figure out how to stop these attacks from occurring.

First, let’s talk about what a SQL Injection Attack really is. Some people think it’s a virus of sorts, that is “inside your site.” Not the case. These are bot attacks by other virus infected computers. They simply use a brute force approach of scanning URLs that take POST/GET inputs and attempt to send their own data to them.

So, how do you track these down and stop them? For web sites powered by Microsoft’s IIS, here are our suggestions:

1. Look at your IIS logs
   Try searching for the word “DECLARE” or “EXECUTE.” If you’ve been hit by an attack, these will more than likely show up in your IIS logs — at least for any attack that was attempted using “GET” posts. If you do find any instances of “DECLARE” or “EXECUTE” these are the pages to start with.
2. Use centralized database connection handling
   Simple, make a centralized file (e.g. connection.asp if you are using ASP) that handles all of your DB access. This way, it’s easier to make sure that you are SQL encoding your pages. You can easily search queries for “DECLARE” and “EXECUTE” and stop the attacks dead in their tracks.
3. Implement a site wide solution
   If you are running your own server, we highly recommend ISAPI_Rewrite from HeliconTech (http://www.helicontech.com/isapi_rewrite). This is an ISAPI filter that allows you to do a variety of things, including scan URL data. This will stop 99% of attacks without changing ANY code on your site!\
4. Never use “sa” as your database user, create a user for the database you’re working from and then remove privileges to read the master dbo. This prevents the attacker from “sniffing” your database structure, however, these attacks have evolved so that sanitized stored procedure based attacks happen even with these types of security in place. See http://www.ngssoftware.com/papers/more_advanced_sql_injection.pdf for more information on this and other ideas in preventing SQL Injection attacks.

If you’ve ever been scratching your head wondering where the leak in your programming is (or have taken over a project from someone else) then the best way to determine through which page the attack happened is by checking out the IIS logs.

There are many scripts available to clean up, but the best tip is to backup hourly, and to follow best practices (some of which I highlighted here). Good luck

So what’s this CLSID {ABF05265-635E-44B0-A28F-AEA45247ACA0}?

I was working on a server when I noticed an error in the Windows Event Log in this format

Event Type: Error
Event Source: DCOM
Event Category: None
Event ID: 10016
User:
Computer: SERVER1

Description:
The application-specific permission settings do not grant Local Launch permission for the COM Server application with CLSID {ABF05265-635E-44B0-A28F-AEA45247ACA0} to the user SID (S-1-5-21-573225893-205518295-3307690801-69150). This security permission can be modified using the Component Services administrative tool.
Digging up on the registry gave me this information. The CLSID value {ABF05265-635E-44B0-A28F-AEA45247ACA0} happens to be for Microsoft.SqlServer.Dts.Server.DtsServer.

I checked the Component Services console and opened the Properties for the MSDTSServer DCOM application. I’ve added the user account mentioned in the error message in the Launch and Activate Permissions and that did the trick

Using a Linked Server in SQL 2005 express to connect to Excel

I’m quite frustrated when it comes to the DTS options lacking in SQL 2005 Express, especially when it comes to importing excel spreadsheets into a table so I can reference it easy within a T-SQL statement to import data into my already existing relational database.

Eventually I found a little gem that seemed to work:

1
2
3
4
5
6
7
8
9

EXEC SP_ADDLINKEDSERVER 'EXCEL2',
	'Excel',
	'Microsoft.Jet.OLEDB.4.0',
	'D:\data.xls',
	NULL,
	'Excel 8.0',
	NULL
 
SELECT * FROM excel2...a$ WHERE [Whatever COLUMN] IS null

Effectively you alias the excel document as a linked sql server (in this example, excel2). You only have to do this once. Once it’s setup it works until you de-link the server alias (or you move the data source)

This helped me tremendously in getting data and querying it with T-SQL without having to import it like I’m used to with DTS. You might want to be on the look-out for language differences between the two, but a simple COLLATE DATABASE DEFAULT should do the trick in most instances when inner join’ing or comparing two different server data.

Listing of all ports

I was trolling for information and thought that this website was very helpful. It lists all Network Ports used by Microsoft Products. This is a handy reference for anyone setting up firewall rules or wanting to connect to specific ports using their own 3rd party software

Restoring a corrupt MetaBase in IIS 6.0 on Windows 2003

Recently I had the unforunate event of “Interface not Supported” when I clicked on the Home Directory of a website hosted on our Windows 2003 Standard machine. I’ve followed (or tried to) several people’s advice. Talk of metabase.bin etc confused me as there were no such files!

So, on my own destructive mission, I saw a “History” folder (other than the “MetaBack” folder) in Inetsrv (under the System32 directory)

Low and behold, there were several Metabase.xml files. So I took the oldest one (which was an hour before the corruption), ran iisreset /stop. Copied the file and overwrited MetaBase.xml and ran iisreset /start again.

Booya. It worked again. FUCK YOU MICROSOFT