Create failed for User 'XXX' when restoring a database

Recently I was doing the database (let's say: 'restoreTest') migration in MSSQL 2008 from one instance to another.

There's an existing login (let's say: 'restore' in picture below) that I need to bring along with this 'restoreTest' database migration.

1) When I went to create a new login and assign the access-right of the newly-created 'restore' account to the new 'restoreTest' database restored in the new instance,

2) I encounter this error message:

Create failed for User 'XXX'
User, group, or role 'XXX' already exists in the current database. (Microsoft SQL Server, Error: 15023)

3) The new 'restore' user is created but it's not associated with  'restoreTest' database.
I was so curious what went wrong. After a check, I  found the user 'restore' was backed-up along with the database.

4) After I deleted the user 'restore' that comes in the newly-restored 'restoreTest' database & , and re-assign it, then everything is OK.

Creating Linked-server in MSSQL

I come across a situation where I need to import the data from one instance to another, in MSSQL. So, I add the steps here to see it might help.

1) At source DB where we want to retrieve the data, create a new login account, eg. "test" with password "123".

2) Use the neccesary authetication, choose to untick "Enforce password policy" for our testing purpose.

3) If the db name in source DB is called "testing", maps the test account "test" to this db, with appropriate schema. In this case, it's "dbo". Add the role as "db_datareader" as well.

4) At destination DB where we want to store the data, create a same new login account created in step (1), eg. "test" with password "123", using SA account.

5) Use the neccesary authetication, choose to untick "Enforce password policy" for our testing purpose.

6) If the db name in destination DB is called "testDB", maps the test account "test" to this db, with appropriate schema. In this case, it's "dbo".

7) Add a linked-server in destination DB, you can either use SQL script or GUI.

8) You can verify the available linked-server in the destination DB, thru' Catalog Views: sys.servers

9) Now, log-in as "test" user.

10) So, you should be able to query the source DB data by using a fully-qualified name.

You can get my projects at Softpedia now, hehe

Softpedia Editorial Team informed me that they have included my projects in their sites. 

1) Analog Clock - A small desktop gadget that will display the current time (Downloads: 44)

2) SQL Management Console 0.1.1.29977 -Query pane and result pane (Downloads: 50)

Again, really thanks to them.

MSSQL 2008 System View release!

Are you a DBA? Want to know how the overall architecture of MSSQL? Ever wonder how you can manage the databases easily? How you can create a version of simple MSSQL Management Console better than mine? Yes, now you can see how it works through the entity-relationship model (ERM) at here.

Start from sys.objects, sys.columns, ..., you can easily manipulate your T-SQL scripts within the following categories of component:

• Objects, Types and Indexes
• Trace and Eventing
• Linked Servers
• Common Language Runtime
• Partitioning
• Database Mirroring
• EndPoints
• Database and Storage
• Execution Environment
• Service Broker
• Security
• Transaction Information
• Resource Governor
• Server-Wide Information
• Full Text Search
• Extended Events

p/s: During last year PDC 2008, .NET FX team has released a selection of new types and namespaces introduce in FX4 at Brad Abrams's blog. From there, you can download 2 versions:

1. PDF version
2. DeepZoom version

Cannot resolve the collation conflict on temporary table?

"Cannot resolve the collation conflict between "Collation_A" and "Collation_B" in the equal to operation."

When I am using MSSQL 2000, it's normal when I hit this kind of collation problem on physically-existed table. Either the source database is conflict with new database, due to an upgrade from older version of customized database, or from other production site from different country.

But recently, I hit this issue at Temporary Tables (because I changed a new job and performed a new installation on my company laptop).

Let's say you have a following scripts written at stored procedure(SP):

-- Use AdventureWorks
create table #tempTable (ID int IDENTITY(1,1) NOT NULL ,
column1 varchar(200) NOT NULL)
INSERT INTO #tempTable
Values('test')
create table tempTable (ID int IDENTITY(1,1) NOT NULL ,
column2 varchar(200) NOT NULL)
INSERT INTO tempTable
Values('test')
Select * from #tempTable t
inner join tempTable p
on t.ID = p.ID
where t.column1 = p.column2

--drop table #tempTable
--drop table tempTable

and when you execute it, you will get the following error message:

Msg 468, Level 16, State 9, Line 10
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

At the first look, I was confused what went wrong. ( Of course you can suggest assign the associate collation will do, but what if there's tons of SP you need to modify? And even worst, they are decrypted!)

Of course the best practice would be something like this:

CREATE TABLE #TestTempTab
(PrimaryKey int PRIMARY KEY,
Col1 nchar COLLATE database_default
)
as in Setting and Changing the Column Collation, but I doubt most people do that.

So first I traced down from the definition of the Temporary Tables. From MSDN:

You can create local and global temporary tables. Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions. Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).
...

The full name of a temporary table as stored in the sysobjects table in tempdb consists of table name specified in the CREATE TABLE statement and the system-generated numeric suffix.

I queried from tempdb at System Databases with this query: (Note: you must use "Like" statement for the name)

use tempdb
Select collation, * from sysobjects so
inner join syscolumns sc
on so.id = sc.id
where so.name like '#tempTable%'

Why? Why it's collated in "Latin1_General_CI_AS"? Then I checked the database collation for this "tempdb" database and server instance:

Why all in "Latin1_General_CI_AS" collation? Then I went through the screenshot I took when I installed the MSSQL 2005 Developer Edition long long time ago (I have a habit to store the installation settings for future purpose, as in this case), I found this:

I switched the collation to Collation designator, which default to "Latin1_General_CI_AS" collation, as in my OS culture! I just realized I misused the in-appropriated collation with the current database (in this case "AdventureWorks", which uses "SQL_Latin1_General_CP1_CI_AS" collation.

So now, what is the solution?

I thought the faster way should be alter the "tempdb" database collation like this :

or from script:

USE [master]
GO
ALTER DATABASE [tempdb] COLLATE SQL_Latin1_General_CP1_CI_AS
GO

But I get this error message:

I searched the MSDN/BOL, and I found this: Setting and Changing the Server Collation

So I inserted my installer in CD-ROM and tried to use this command:

start /wait setup.exe /qb INSTANCENAME={my SS2005 instance name} REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD={my SS2005 instance sa password} SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI

but the wizard came and went but nothing happened:

So I tried to look at the summary.txt produced, as suggested in SQL Server 2005 Setup Log File. Nothing too!

Then "SQLSetup0011_(pcName)_Core.log":

..
Error: Action "LaunchPatchedBootstrapAction" threw an exception during execution. Error information reported during run:"C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\setup.exe" finished and returned: 0Aborting queue processing as nested installer has completed.

I also try to repair it. But there's no such options.

Eventually I uninstalled it. Haha.

It's SQL injection, Not IIS vulnerability

Half A Million Microsoft-powered Sites Hit With SQL Injection.

Few days ago, people thought it was an IIS vulnerability but eventually it is a SQL injection. And Aaron Bertrand have few pieces of advices for web developers:
1) Do not expose your SQL Server to the Internet directly.

From TechNet: A common step in troubleshooting connectivity problems is to use the Ping tool to ping the address of the computer to which you are trying to connect. When you ping, you send an ICMP Echo message and get an ICMP Echo Reply message in response. By default, Windows Firewall does not allow incoming ICMP Echo messages and therefore the computer cannot send an ICMP Echo Reply in response. To configure Windows Firewall to allow the incoming ICMP Echo message, you must enable the Allow incoming echo request setting.
Beware if you turn this on!

2) Make your passwords strong.
3) Follow the principle of least privilege Always use stored procedures, or at least parameterized statements .
4) Use TRY/CATCH to return more generic error messages.
5) Do not store passwords in your Users table.

And some tips from Open Web Application Security Project (OWASP), there're few ways for testing the SQL Server:
1: Testing for SQL Injection in a GET request.
2: Testing for SQL Injection in a GET request (2).
3: Testing in a POST request
4: Yet another (useful) GET example
5: custom xp_cmdshell (Especially those who are still using MSSQL 2000!)
6: Referer / User-Agent
7: SQL Server as a port scanner
8: Upload of executables
9: bruteforce of sysadmin password.

Have you tried it on your server?

First release of SQL Management Console

Finally, I decided to release the second .NET application I am developing: SQL Management Console, now. This version 0.1, which is still in beta phase, still has a lot of room for improvement. Use at your own risk.

Here are some of the screenshots of it:
1) Login screen


2) Main GUI with MSSQL 2005 in action

3) Main GUI with MySQL 5.0 in action

4) Object scripting

5) Dependency viewer

For this version, it will suport MSSQL 2000 & 2005, MySQL 5.0. I am planning to include PostgreSQL connector in the next version. The features include in this version are :

1. (IDE-wise) Open/Save/Drag-and-Drop file
2. (IDE-wise) query pane and result pane
3. (MS SQL 2000 & 2005) Object browser for system objects (Scalar function, Stored procedure, System table, Table function, Trigger, User table, View)
4. (MS SQL 2000 & 2005) Object browser for security (Users, Roles, Schemas)
5. (MS SQL 2000 & 2005) Object browser for constraints, columns, parameters (Columns, Keys, Constraints, Triggers, Indexes, Parameters)
6. (MS SQL 2000 & 2005) Dependencies viewer for objects (Scalar function, Stored procedure, System table, Table function, Trigger, User table, View)
7. (MS SQL 2000 & 2005) Object scripting for non-encrypted objects (Scalar function, Stored procedure, Table function, Trigger, View)
8. (MySQL 5.0) Object browser for system objects (Scalar function, Stored procedure, System table, Trigger, User table, View)
9. (MySQL 5.0) Object browser for security (Users, Privilege)
10. (MySQL 5.0) Object browser for constraints, columns (Columns, Keys, Triggers)
11. (MySQL 5.0) Object scripting for non-encrypted objects (Scalar function, Stored procedure, Trigger, View)

Since parts of the source code is came from MSDN, thus I think it's good to release it under Microsoft Public license (Ms-PL) to prevent any infringement, though the chance are very little.

Since I am not an expert on database, feel free to give to any constructive comment and recommendation. I will try to improve it.

You can get the source code at here and executable files at here.

MSSQLSERVER & SQLSERVERAGENT (MS SQL 2000) failed after changing the password in active directory

PC must change the password periodically to maintain security, though you still have choice to not to do so if you work alone in default Windows domain. But when it comes to active directory, most MIS guys enforce user to change it through the AD management tools. Thus those softwares in your PC that ties to this AD account for login should be smart enough to changed accordingly.

But in MSSQL 2000, this is not the case. After changing the new AD password, the MSSQLSERVER & SQLSERVERAGENT will not be updated and it will give you error message (if you start it from SQL Server Enterprise Manager: Server Group->[Instance]->Management->SQL Server Agent):

"An error 1609 - (The service did not start due to a logon failure) occurred while performing this service operation on the SQLServerAgent service."

If you start it from Services.msc(Go to command prompt, type: "services.msc"), you will get the same logon error:

Due to the login account has changed, now MSSQL and SQL Server Agent cannot identify you correctly anymore, since the password it keeps is the one you saved last time. You must change the password to let it works.

1) Right-click the particular service (MSSQLSERVER or SQLSERVERAGENT).

2) Choose "Properties"

3) Select "Log On" tab.

4) Change the new AD password as shown, that is!

Decrypt the stored procedure, and etc

When you try to hide something from someone, people always have their own way to dig it out. Just like you thought 128-bit encryption is un-decryptable, especially on symmetric encryption , people still can know you initialization vector (IV) by de-compiling your source-code using deflector, if you did not obfuscate it (Oops, or even obfuscation is not safe too?). Then all your effort on encryption/information-hiding is wasted. Same goes to MSSQL 2000 built-in encryption for stored procedures (SPs/sprocs), etc.

The syntax for MSSQL 2000 built-in encryption on CREATE PROCEDURE is:

CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE ENCRYPTION RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]

To decrypt it, for novice user, you can download the freeware (Unless you wanna pay the money for this software?) called "dSQLSRVD", just log in and find those SPs with checkboxes checked, highlight it and click "Save", you can get the content of the stored procedure you desire. Easy, huh?

For average developer, maybe you can go to this site (please take note this method seems have limitation on the size of nvarchar(4000), you might need to roll back if your encrypted SP is long), or this site, and try to come out your version of freeware, though you can find a ready-made workable sql script if through a thorough search.

Final piece, Windows applications is too common and thus make it too popular for developers to crack it. You better depend on your own.

p/s: In MSSQL 2005, there's few new features added,
1) EncryptByAsymKey
2) EncryptByCert
3) EncryptByKey
maybe it's worth for try.

Trivial but also Tricky : Result of using "is NULL" & "= NULL" in T-SQL is different

For the T-SQL (here I use MS-SQL), especially when you write stored procedure, you will normally receive parameter(s) from .NET/Java or any equivalent language to manipulate your client records. This is a normal design of three-tier application (Presentation Layer represents the UI that interact with user; Business Logic layer to code all your business logic; Data Access layer to deal with various kind of data storage apps)

For these received parameters, normally you will use some of it to retrieve (or perform checking or validation) some other data from other tables. You will write any T-SQL to perform SIUD(Select/Insert/Update/Delete) on records based on the business logic in database. In order to SUID on these records, your WHERE-statement must be written precisely, and of concisely. If the WHERE-statement is wrong, then the records SUID-ed will be corrupted.

Let's have simple example:

1) Create a simple table with nullable columns

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_Test]') and

OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[_Test]

GO

CREATE TABLE [dbo].[_Test] (

[col1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[status] [int] NULL ) ON [PRIMARY]

GO

2) Insert 2 sample records into it

insert into _Test(col1, status)

values('First row', NULL)

insert into _Test(col1, status)

values('Second row', 1)

3) Create a stored procedure to determine the record could be found in database or not

IF EXISTS (Select * from sysobjects where name = '_spTest' and xtype = 'P')

DROP PROCEDURE _spTest

GO

CREATE PROCEDURE dbo._spTest

@p1 int

As

IF EXISTS ( Select * from _Test where status = @p1)

PRINT 'Records found !'

ELSE

PRINT 'No records found !'
GO

4) Now try to execute it

execute _spTest 1

execute _spTest NULL

Oh, where's another records?

6) And this is quoted from MSSQL BOL (Book On-Line) / T-SQL Reference:
To determine if an expression is NULL, use IS NULL or IS NOT NULL rather than comparison operators (such as = or !=). Comparison operators return UNKNOWN if either or both arguments are NULL.

Now, you realized you used the comparison operators to execute it unknowingly!

7) So, in order to access the particular data accurately, the correct stored procedure should be written (Of course, this is simple scripts only, COALESCE or any other keywords can help you to SIUD the huge records efficiently) as

IF EXISTS (Select * from sysobjects where name = '_spTest' and xtype = 'P')

DROP PROCEDURE _spTest

GO

CREATE PROCEDURE dbo._spTest

@p1 int

As

IF (@p1 IS NULL)

BEGIN

IF EXISTS ( Select * from _Test where status IS NULL)

PRINT 'Records found, with status is null!'

ELSE

PRINT 'No records found with status is null!'

END

ELSE

BEGIN

IF EXISTS ( Select * from _Test where status = @p1)

PRINT 'Records found!'

ELSE

PRINT 'No records found!'

END
GO

8) Now try to execute it again

execute _spTest 1
execute _spTest NULL

9) Correct!