October 20th, 2003, 11:19 PM
Poor SQL Server Programming Part 2
Unfortunately, in my current job -- this is only the beginning of my
problems. I have other servers/applications that I'm cleaning up --
I'm currently in the process of cleaning up and hardening a SQL Server box
that has been left alone for about a year now. A client applications at our
customer sites access the SQL Server to pull up forms, etc., within their
The issue I have is that most of the 22 databaes on the box are average of
18MB. Unfortuantely, the log file for almost all of them was over 1.5GB!
Since there is really nothing in the SQL databases other than pointers to
mainframe data, the data changes often enough that it doesn't warrant a
backup; sinsce it can easily be regenerated from mainframe.
At any rate, I had the pleasure of truncating the transaction log, turning
on truncate on checkpoint, and resizing the databases. It amazes me how
this simple, DBA 101 fact, is simply offer looked and never included on any
best practices or set up instructions from the vendor. The folsk that
installed this Server and application knew nothing outside of following the
vendors absolute instructions. Nothing more and nothing less.
On this same server I noticed that there were 22 users (1 per database) that
were created on the box. The associate that I contacted about these users
said that the application required these users. I checked the permissions
and the users were DBO of their database. No biggie. Unfortuantely, the
password is the same for all databases... stupid. Now any customer could
theoritcally get into anyones' database.
I also noticed there was another user that was not assigned to one of our
customers. The associate that I was consulting about this user said he did
not know what it was or who created it. I quickly did an sp_who to see all
active users on the box to see if it was being used.
To my dismay, every one of our 22 databases had at least 10 connections per
database using the same user account. None of them were using the customer
specific login... This user, of course, was System Administrator. Now
every client at all 22 customers are accessing their database with
SystemAdmin priviledges. Not cool.
To make matters worse, I called the vendor to find out what the password was
for this unknown user. They refused to tell me and said it would break the
security of their application. I then went on to say that it was
SystemAdmin and now exposed every customer on that SQL Server box to a
hacker. Not only corrupted their databases, but all databases. Also
mentioned how SQL Server password could be sniffed over the network. He
later got back to me after speaking with his developer and said that they
would not change it sincse the application required these permissions to
create tables and/or users and the username/password was hard-coded in the
application. He told me if I thought I could hack it -- to be his guest.
That was his first big mistake -- never dare me with something that I'm
bringing to your attention. In closing, I told him that most auditors do
not like seeing 22 end customers and x number of clients per customer
sharing the same application login--especially if they are all SystemAdmin.
The conversation basically ended with nothing will be done.
THE HACKING BEGINS:
Since they refused to tell me a password that their application required on
my SQL Server box, and refused to limit the roles/permissions this user
required, my mission was obvious. I ran a network sniffer on our local SQL
Server box and waited for a cllient logon. As soon as I captured enough
frames, I began sifting thru the frames one by one until I found a user
signing on with the name I was looking for. After a few minutes, I was able
to locate the weak encrypted bytes that Microsoft uses to encode the
I wrote the bytes down, took out my hex/bin calculator and my ASCII chart,
and deciphered their weak password. I was terribly surprised at what I
found. Their hardcoded password was a password that clearly showed how
arregant they are. It was a word referencing money... (as if they're
sucking all our money from us!) The password ws only 8 characters long.
Consisted of only lowercase letters. No mix case, no numbers, no nothing.
Any brute force or dictionary attack woud have guess the password.
I then started Query Analyzer and logged into the DB with SystemAdmin
priviledges by using their Username/Password. I was Admin now -- and I
could do anything.
The next day, I called them back and told them I knew their password and I,
like anyone else at our customer sites, could easily hack the server if they
are determined to do so. He did not believe me. I gave him the password
and he was shocked that I got it. I'm thinking to myself now that these
guys should stick to mainframe programming because they know nothing about
PC or client/server programming or security....
I meantioned to him how they have a few options (which clearly they have no
idea what I mean, since they don't know SQL Server)...
1) Anyone can guess any password for SQL Server using SQL Server
authentication. I do not expect them to get around this. However, I do
expect them to make the password harder to guess by using mix case and
numbers. NT Authentication won't be an option since customers are not on
2) They could use the multi-protocol library to encrypt. Again, this can be
hacked, but it would make it more secure.
3) They could use a SQL Server application role.
4) They could limit the permissions and users to one user per customer
database. Remove SystemAdmin role. And only give Create Object, etc.
permissions that application requires to add/modify/delete objects within
that customers' database. At the very least, if customers network is
compremized, then only that customers database would be affected -- not all
He then said he was run my issue up the flag pole and get back to me. That
was 1 week ago and I haven't heard anything. I'm still waiting...
I'm just amazed at how anyone can release applications in this way. I'm
even more amazed how this gets pasts all our highly paid IT administrators
and developers and auditors!!!
I'm just a lonely SQL Server, W2K user that enjoys these applications as a
hobby and enjoy programming in Perl, VB, C, ASP, as a hobby. These other
folks are highly paid employees and companies that clearly know nothing --
I have other servers I can tell you about -- but I'll save those for