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

How to move domain to a different account in HELM 4

HELM 3 was easy to administrate, a multitude of utilities and tools existed helping us with mundane tasks, one of which is to move a domain from one account created on HELM to a different account. HELM 4… no such luck with a utility… but opening up the database. Checking the Accounts table for the AccountId you need to move a domain to… and then opening the OnlineServices table, will show you all the domains added on HELM and their associated AccountId’s. To change the domain linked to an account, it’s as easy as just changing the AccountId to the one you prefer viewing.

Hope this tip helps some folk as I could find absolutely nothing to indicate how to do this and had to figure it all out by myself.

Changing Table Ownership in SQL Server

Recently I had the unfortunate event of a live server being setup and used by only 1 user which wasn’t dbo. Either the programmer didn’t know any better or… well… he was the village idiot… anyway, so… on with the code. Here is a simple T-SQL statement that will get and loop through all Stored Procedures, Tables and Views and update them to use dbo (or any other user you’d like):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

DECLARE @tbl sysname
DECLARE tblcur INSENSITIVE CURSOR FOR
	SELECT name FROM sysobjects
	WHERE xtype IN ('U','V','P') AND uid = user_id('olddbuser')
OPEN tblcur
WHILE 1 = 1
	BEGIN
		FETCH tblcur INTO @tbl
			IF @@fetch_status <0
				BREAK
 
			SET @tbl = 'olddbuser.' + @tbl 
			EXEC sp_changeobjectowner @tbl, 'dbo'
	END
DEALLOCATE tblcur

Using openrowset to connect directly to another database without a linked server and execute a stored procedure

1
2

SELECT *
FROM OPENROWSET('SQLOLEDB',database_location;username;password, 'set fmtonly off exec database.dbo.stored_Procedure') AS Whatever

The above piece of code is self-explanatory. I’ve only used this in SQL 2005 though, so if it doesn’t work on SQL 2000 don’t come crying to me.

Replace database_location with the IP/Destination of the server you’d like to connect to
Replace username/password with… well… do I really have to explain this?
Replace database with the catalog/database you’d like to query
Replace stored_Procedure with the stored procedure you’d like to execute
The “As Whatever” is necessary. You could name this anything you’d like.

I’ve used the above to query a database directly for data extraction/manipulation. You could go as far as to inner join this in your normal queries if you’re not doing an update/add cursor loop on this.

Enjoy. Took me a while to figure it out

IP to Country – Convert IP Address to IP Number

Here is a simple SQL Statement that you can use in a stored procedure to determine the IP number of an IP address to compare with any IP to Country database:

1
2
3
4
5
6
7
8
9
10
11
12

DECLARE @ip VARCHAR(15),@1st INT,@2nd INT,@3rd INT,@4th INT
 
SET @ip = '41.243.224.9'
SELECT @1st = LEFT(@ip,CHARINDEX('.',@ip)-1)
SET @ip = RIGHT(@ip,LEN(@ip)-CHARINDEX('.',@ip))
SELECT @2nd = LEFT(@ip,CHARINDEX('.',@ip)-1)
SET @ip = RIGHT(@ip,LEN(@ip)-CHARINDEX('.',@ip))
SELECT @3rd = LEFT(@ip,CHARINDEX('.',@ip)-1)
SET @ip = RIGHT(@ip,LEN(@ip)-CHARINDEX('.',@ip))
SELECT @4th = @ip
 
SELECT ((@4th)+(@3rd*256)+(@2nd * 65536)+(@1st * 16777216))

This allows you to have a SQL procedure take the variable of an IP address and not have to convert the IP address in code first. This should work for MySQL as well with minor changes.

The formula is pretty simple and self-explanatory. As soon as I find a reliable (mostly complete) IP to Country database, I will edit this post to reflect it, thus far I haven’t found any to even pick up the address range my country is in which is a real shame. Not even the open source PHP project ip2c can pick it up, which makes me doubt it’s ability to help in any program developed to determine the country automatically from the IP address.

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.

How to setup a Windows 2003 Web Server

Every time we setup a W2K3 box as a Web Server we do the following

Stage 1
1.1 Copy i386 to c:\
1.2 Configure Windows Permissions on all partitions
- Leave
Administrators (Full)
System (Full)
Remove Everything Else
1.3 Rename Administrator
1.4 Rename Machine
1.5 Reboot
1.6 Install Windows Updates (Not the .NET Frameworks)
1.7 Add IP’s to TCP/IP
1.8 Add DNS suffix to TCP/IP (Only if required)
1.9 Disable Shutdown Event Tracker
1.10 Reboot

Stage 2
2.1 Install Support Tools
2.2 Install Resource Kit Tools
2.3 Install KB908521 (Not needed if w2k3 SP2)
2.4 Install SNMP
2.5 Install SNMP Informant
2.6 Configure Windows Time
2.7 Reboot

Stage 3
3.1 Install R2 (Only if you want it)
3.2 Reboot

Stage 4
4.1 Setup Automatic Updates
4.2 Setup MS DNS
4.3 Set TCP/IP to Local DNS
4.4 MSTDC Fix

Stage 5
5.1 Install IIS
5.2 Allow direct Metabase Edit
5.3 Set IIS IP’s to be listened on
5.4 Restart IIS
5.5 Set Default Documents
5.6 Set W3SVC Logs Params
5.7 Install FastCGI
5.8 Install PHP 5.1 & 5.2 (FastCGI Mode)
5.9 Install Zend Optimizer (32bit edition)
5.10 Install Perl
5.11 Install Python
5.12 Configure IIS SMTP
5.13 Disable IIS SMTP Socket Pooling
- http://www.isaserver.org/tutorials/i…etpooling.html
5.14 Reboot

Step 6
6.1 Install .NET Framework 2.0
6.2 Install .NET Framework 2.0 SP1
6.3 Install .NET AJAX 1.0
6.4 Install .NET Framework 3.5 (Make sure you use the installer without SP1)
6.5 Install .NET Framework 3.5 SP1 (Only if using Helm 4.1)
6.6 Reboot

Step 7
7.1 Install ISAPI_ReWrite
7.2 Install JMail.NET
7.3 Install W3JMail
7.4 Install Hotlink Blocker
7.5 Install IIS Password
7.6 Install Winrar
7.7 Install Persists ASP Email
7.8 Install Persists ASP JPeg
7.9 Install Persists ASP Upload
7.10 Reboot

Naturally if the Box isn’t going to be a DNS Server you wouldn’t install MSDNS, instructions apply to both 32bit and 64bit w2k3. I’m not going to explain each step in detail the info is just so you can see what order we do things. It has been very well tested and we haven’t had any issues

At each step we test everything we install so we make sure PHP is working correctly before proceeding onto the next step. If you need to install MySQL or MSSQL I’d do it at Step 8. Once everything is setup you would install Helm as a remote or control.

How to show only the time value in getdate()

I was busy messing around today and did the following I think some people will appreciate when I just wanted to compare the time of day and not the date:

1
2
3
4

IF DATEADD(DAY, -DATEDIFF(DAY, 0, GETDATE()), GETDATE()) > '15:30:00.000'
 BEGIN
    PRINT 'yay'
 END

Date Defaults – What every programmer should know

A few years back when I started programming my first task was to code an intranet system able to track meetings and certain tasks employees did throughout the day based on a calendar. This was before I knew what power SQL Server 2000 (and now 2005/2008) had and how easy it was working within those systems.

MS Access (which changed a lot since 1999) had one flaw. It always kept the date formats in mm/dd/yyyy (America is the “world” hahahahaha) so this made my job increasingly difficult trying to handle the dates on a South African based time (dd/mm/yyyy)

I struggled hard to understand the shameless act to which MS Access butchered my date inputs but I couldn’t help but think that this CAN’T happen to all programmers!!

After a lot of heart-ache and a lot of sleepless nights I finally found the silver bullet to the werewolf that was MS Access (and other servers worldwide running on different timezone settings)

dd-MMM-yyyy!!! Translated into: 01-Jan-1999 for example, you can insert ANY date with 100% surety that your sleep WILL guaranteed. This universal date format will work in ANY (yes you read it! ANY) database known to man and even though your raw data may look like 09/02/1999 in the database, it will always insert the correct date in the correct format in the database irrespective of what timezone the server is set on!

Classic ASP exampes of reading the date out is:

1

Day(date()) & "-" & Left(MonthName(Month(Date())),3) & "-" & Year(Date())

SQL Server example would use DateName instead of MonthName (which can be abbreviated without the Left() function I had to use in ASP) and of course, date() = getdate() in SQL Server

I would always recommend reading it out of the database (whenever possible) with stored procedures in the correct format to begin with. But it’s up to you to decide how to use this gem of knowledge I just thrown at you. And if you’re reading this thinking “But I already knew this!” then think again. Unfortunately, in 2009, there are still quite a few programmers unable to grasp simple concepts like timezones and date handling.

Remember kids. Making mistakes when you’re still new in the game is one thing. Brandishing a CV (Resume) of 3 years+ experience and still making these mistakes is just plain wrong… you chose a field where you have to evolve and look for answers constantly, don’t stagnate… I’ll haunt your dreams if you do