Change Lincense Key

We currently have a SQL cluster which was built on pirated license keys.
This was a managerial mistake let me point out.~
Can we change the license key to legal copy without re-installing SQL? I
see that they license key is stored in the registry, but it appears that the
Product ID is generated randomly.
Any help is appreciated.Hi
You may want to try the rebuild registry option in the setup program or see
http://tinyurl.com/ad484
John
"Andrew" wrote:
> We currently have a SQL cluster which was built on pirated license keys.
> This was a managerial mistake let me point out.~
> Can we change the license key to legal copy without re-installing SQL? I
> see that they license key is stored in the registry, but it appears that the
> Product ID is generated randomly.
> Any help is appreciated.
>

Change Lincense Key

We currently have a SQL cluster which was built on pirated license keys.
This was a managerial mistake let me point out.~
Can we change the license key to legal copy without re-installing SQL? I
see that they license key is stored in the registry, but it appears that the
Product ID is generated randomly.
Any help is appreciated.
Hi
You may want to try the rebuild registry option in the setup program or see
http://tinyurl.com/ad484
John
"Andrew" wrote:

> We currently have a SQL cluster which was built on pirated license keys.
> This was a managerial mistake let me point out.~
> Can we change the license key to legal copy without re-installing SQL? I
> see that they license key is stored in the registry, but it appears that the
> Product ID is generated randomly.
> Any help is appreciated.
>

Change Identify/Primary Key Column Data Type?

Hi ...

I've

taken over a project where the tables were created with

identity/primary key columns of type DECIMAL(12,0). The latest addition

to the project is to replicate data down to Pocket PC applications.

Replication requires that identify/primary key columns be of type

INT/BIGINT.

I've attempted to ALTER TABLE xxx ALTER COLUMN yyy

BIGINT; and it fails. Failed due to all the foreign key constraints that have been created. While it didn't give any error messages associated with the the field being the PRIMARY KEY - I'm assuming I may get that error as well.

I then did an ALTER TABLE xxx NOCHECK CONSTRAINT

ALL; for every table in the system to disable checking of foreign keys

and then attempted to alter the column to a bigint and it still failed.

How

can I change the column from Decimal to BIGINT - or do I have to create

new tables, import all the data, get rid of the original tables? Please

tell me I don't have to do the latter.

Thanks ...Unfortunately the only way is to recreate the table or drop & recreate the column. This would require removing the existing constraints (PKs, FKs) and recreating them. Alternatively, you can create a view on the table(s) that does the conversion and replicate those. But this may or may not work depending on your replication scheme and you will have to ask in the Replication newsgroup.

Change Clustered Index to Non-Clustered Index

When I create a table in Enterprise Manager, the Primary
Key is created as a Clustered Index (As there is no other
Clustered Index exists in that table).
However, when I attempt to change the Clustered Index to a
non-Clustered Index, it says that "Cannot convert a
Clustered Index to an nonclustered index using the
DROP_EXISTING Option".
I would like to know
1) Is it possible to change the Clustered Index to Non-
clustered Index in Enterprise Manager OR we have to change
it in Query Analyzer ?
2) When we create a table in Enterprise Manager, can we
specify a column as a Clustered Index (Instead of creating
Clustered Index in the Primary Key)?
ThanksRoger
DROP TABLE TEST
CREATE TABLE TEST
(
COL INT NOT NULL PRIMARY KEY
)
--Run this sp to make sure you have clustered unique index
SP_HELPINDEX TEST
--other way to create clustered index
CREATE TABLE TEST (COL INT)
GO
CREATE UNIQUE CLUSTERED INDEX Idx1 ON TEST(COL)
--change clustered index to non_clustered
CREATE UNIQUE CLUSTERED INDEX Idx1 ON TEST(COL)
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'Idx1')
DROP INDEX TEST.Idx1
CREATE UNIQUE NONCLUSTERED INDEX Idx1 ON TEST(COL)
SP_HELPINDEX TEST
"Roger Lee" <rogerlee@.nospam.com> wrote in message
news:04e701c35a5b$1667a1c0$a101280a@.phx.gbl...
> When I create a table in Enterprise Manager, the Primary
> Key is created as a Clustered Index (As there is no other
> Clustered Index exists in that table).
> However, when I attempt to change the Clustered Index to a
> non-Clustered Index, it says that "Cannot convert a
> Clustered Index to an nonclustered index using the
> DROP_EXISTING Option".
> I would like to know
> 1) Is it possible to change the Clustered Index to Non-
> clustered Index in Enterprise Manager OR we have to change
> it in Query Analyzer ?
> 2) When we create a table in Enterprise Manager, can we
> specify a column as a Clustered Index (Instead of creating
> Clustered Index in the Primary Key)?
> Thanks|||Roger
You can change a clustered index to non-clustered in EM.
How did you try to do it? Use the properties window in the
design table pane. (it generates a drop index and create
index stement for you)
You can create a column or indeed multi-column clustered
index on any data you like. Again using EM, the design
table is the easiest way to do it.
Hope this helps.
John|||Dear John,
Does "Design Table Pane" mean the Database Diagram ?
I create a new database diagram with that table and I am
able to chagne the Clustered Index to Non-Clustered Index.
Thanks|||Roger
No not the database diagram.
In EM open up databases on your server. Then open the
database you want. Click on tables. In the pane on the
right, right click on the table you are interested in and
choose 'design table'.
One in the design table view you can open index properties
and there you can do a variety of thing with your indexes
including creating new ones, moving filegroups, make an
index clustered (as long as there is not already one) or
make it unclustered if it already is clustered.
When you exit the design table view it will ask if you
want to save the changes, say yes if you want the changes
you have made to take effect.
Regards
John

change clustered index

Hi,
I have a table with 1.5 million records. It has the primary key as the clustered index now, I would like to change the create_dt as clustered index. When I do reindex, will it block the table? How long will it take? Can I do it in EM from the Design able? ThanksYes you can do it from EM and yes it will block all access to the table for
the entire duration of the process. How long this takes depends mainly on
your hardware configuration.
--
Andrew J. Kelly
SQL Server MVP
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:A0F7FA2F-77F0-486B-99C2-31B2DD2E1409@.microsoft.com...
> Hi,
> I have a table with 1.5 million records. It has the primary key as the
clustered index now, I would like to change the create_dt as clustered
index. When I do reindex, will it block the table? How long will it take?
Can I do it in EM from the Design able? Thanks|||Hi,
This operation has to be done when there is no activity in this table.
Execute the below statements from Query Analyzer, Replace the table name ,
constraint name and column name with yours.
ALTER TABLE dbo.tablename
DROP CONSTRAINT Pkey_constraintname
go
ALTER TABLE dbo.tablename ADD CONSTRAINT
Pkey_constraintname PRIMARY KEY NONCLUSTERED
(
columnname
)
go
Create Clustered index IDX_name on tablename(newcolumn)
Thanks
Hari
MCDBA
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:A0F7FA2F-77F0-486B-99C2-31B2DD2E1409@.microsoft.com...
> Hi,
> I have a table with 1.5 million records. It has the primary key as the
clustered index now, I would like to change the create_dt as clustered
index. When I do reindex, will it block the table? How long will it take?
Can I do it in EM from the Design able? Thanks

change clustered index

Hi,
I have a table with 1.5 million records. It has the primary key as the clust
ered index now, I would like to change the create_dt as clustered index. Whe
n I do reindex, will it block the table? How long will it take? Can I do it
in EM from the Design able?
ThanksYes you can do it from EM and yes it will block all access to the table for
the entire duration of the process. How long this takes depends mainly on
your hardware configuration.
Andrew J. Kelly
SQL Server MVP
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:A0F7FA2F-77F0-486B-99C2-31B2DD2E1409@.microsoft.com...
> Hi,
> I have a table with 1.5 million records. It has the primary key as the
clustered index now, I would like to change the create_dt as clustered
index. When I do reindex, will it block the table? How long will it take?
Can I do it in EM from the Design able? Thanks|||Hi,
This operation has to be done when there is no activity in this table.
Execute the below statements from Query Analyzer, Replace the table name ,
constraint name and column name with yours.
ALTER TABLE dbo.tablename
DROP CONSTRAINT Pkey_constraintname
go
ALTER TABLE dbo.tablename ADD CONSTRAINT
Pkey_constraintname PRIMARY KEY NONCLUSTERED
(
columnname
)
go
Create Clustered index IDX_name on tablename(newcolumn)
Thanks
Hari
MCDBA
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:A0F7FA2F-77F0-486B-99C2-31B2DD2E1409@.microsoft.com...
> Hi,
> I have a table with 1.5 million records. It has the primary key as the
clustered index now, I would like to change the create_dt as clustered
index. When I do reindex, will it block the table? How long will it take?
Can I do it in EM from the Design able? Thanks

Certificates versus Keys

Hi,

A few questions:

Are there best practices regarding when a Certificate is appropriate for encrypting data versus using a asymmetric key?

Also, when you create an asymmetric key - aren't you really creating both the public/private key pair?

Lastly, what can a Certificate do that an asymmetric key cannot?

Thanks much,

JoeYes, when you create the asymmetric key, you create a key pair. The same thing happens for certificates if you use the "WITH SUBJECT" clause instead of creating the certificate from a file.

In terms of encryption and signing, what you can do with one, you can also do with the other. The main differences are in how you create them (certificates can be imported from X.509 files, asymmetric keys from assemblies) and the fact that certificates can be backed up to files, while asymmetric keys cannot.

This last point is very important because it means that if an asymmetric key is created internaly, not loaded from a file, then it is not easy (it is possible but not practical) to create the same asymmetric key in another database. So if you do code signing and you want the signature to grant server-level permissions, it will be more convenient to use certificates.

Thanks
Laurentiu|||

That answers my questions Laurentiu. As always, thanks again for your help.

Best Regards,

Joe

Certificate Restore Not working.

I have been trying to use Encryption to encrypt a few key fields. So my first goal was to create all the items I would need to effectivly use encryption, to encrypt a few key fields.

First I created a sample sql script to create a certificate, create a table and insert some test data encrypted. And then made sure I could decrypt the data encrypted with the certificate.

Second step was to test the backing up of the certificate, droping the certificate to similate a restore of the database. And then a restore of the certificate, and then see if the it was still possible to decrypt the existing encrypted data with the restored certificate.

I receive no errors when restoreing the certificate, but it does not properly decrypt the exisiting data.

Can anyone help and point out my mistake?

--Start Sql--

--Create Sample Cert CREATE CERTIFICATE SampleCert1 ENCRYPTION BY PASSWORD = '728AC41753642403251BF8E7233EC0C' WITH SUBJECT = 'Sample Cert for Demo', EXPIRY_DATE = '04/18/2017'; --create Sample table with encrypted version CREATE TABLE Table_1 (Id int NOT NULL IDENTITY (1, 1), SSN_Encrypted varbinary(300) NOT NULL) ON [PRIMARY] GO --insert row with encrypted version. Insert into Table_1 (SSN_Encrypted) values (EncryptByCert(Cert_ID('SampleCert1'),'000-00-0000')) --returns '000-00-0000' for 'SampleCert1' since it was able to decrypt select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted,N'728AC41753642403251BF8E7233EC0C') as varchar(12)) as 'SampleCert1' from Table_1 GO --backup Certificate BACKUP CERTIFICATE SampleCert1 TO FILE = 'D:\Backups\SampleCert.cer' WITH PRIVATE KEY ( FILE = 'D:\Backups\SampleCert.pvk' , DECRYPTION BY PASSWORD = N'728AC41753642403251BF8E7233EC0C' , ENCRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh' ); GO DROP CERTIFICATE SampleCert1; GO --Restore from backup. to simulate a restore from tape CREATE CERTIFICATE SampleCert1 FROM FILE = 'D:\Backups\SampleCert.cer' WITH PRIVATE KEY (FILE = 'D:\Backups\SampleCert.pvk', DECRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh'); --This should return the same ssn as was encrypted, if returns null the restore failed. select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted,N'728AC41753642403251BF8E7233EC0C') as varchar(12)) as 'SampleCert1' from Table_1 --cleanup Removes Certificate from the system DROP CERTIFICATE SampleCert1; --removes the temporary table. Drop table Table_1

I think I found out why your statement is not working as you expected.

In the following query, the certificate is re-created from the backup, but only a decryption password (used to open the PVK file) is specified, but no encryption password is present. This will create the CERTIFICATE in SQL Server with the private key protected by the DB master key.

CREATE CERTIFICATE SampleCert1

FROM FILE = 'D:\Backups\SampleCert.cer'

WITH PRIVATE KEY (FILE = 'D:\Backups\SampleCert.pvk',

DECRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh');

Instead of

CREATE CERTIFICATE SampleCert1

FROM FILE = 'D:\Backups\SampleCert.cer'

WITH PRIVATE KEY (FILE = 'D:\Backups\SampleCert.pvk',

DECRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh',

ENCRYPTION BY PASSWORD = '728AC41753642403251BF8E7233EC0C');

You can verify if my assumption is correct by running the following statement:

SELECT name, pvt_key_encryption_type_desc FROM sys.certificates

If I am right, you can just change the protection mechanism for the existing CERTIFICATE:

ALTER CERTIFICATE SampleCert1

WITH PRIVATE KEY ( ENCRYPTION BY PASSWORD = '728AC41753642403251BF8E7233EC0C' );

Let us know if this information helped.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Well today is not my lucky day I am afraid. I can't verify yet.

Last night we rolled out a policy to enforce passwords complexity, and length requirements.

So I am geting an error just trying to run the same script, even though I am using NT Authentication, and have updated my password to conform to the new policy.

Msg 15118, Level 16, State 1, Line 1
Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.

Does Create Certificate use a different security creditial then the loged in Users?

I even tried using SA with the following set. with the same results.

Code Snippet

Alter Login [sa] with CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

Any more idea's where I need to look to figure this out?

|||

Disabling the password policy checks is not available for cryptographic objects (certificates, keys, etc.), the only part of the policy enforced in such objects is the password complexity for newly created or modified objects/passphrases.

ALTER CERTIFICATE SampleCert1

WITH PRIVATE KEY ( ENCRYPTION BY PASSWORD = '<<new password that complies with password policy>>' );

--returns '000-00-0000' for 'SampleCert1' since it was able to decrypt

select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted, <<new password that complies with password policy>>') as varchar(12)) as 'SampleCert1'

from Table_1

Or if you prefer to use the DBMK to protect your certificate, you can change your query to the following:

select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted) as varchar(12)) as 'SampleCert1'

from Table_1

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thank you for all your help.

Knowing what password I had to fix made it short work geting it working.

Owen.

CD-Key?

Have one Small Business Server Premium with 5 clients and im trying to install Reporting Services but it keep asking for the key, but there was no key with the program.

Im i supposed to use the key for SQL Server, or SBS?

Thanks in advance

Reporting Services is part of the SQL Server license. You should use a SQL Server product key.

CD-Key?

Have one Small Business Server Premium with 5 clients and im trying to install Reporting Services but it keep asking for the key, but there was no key with the program.

Im i supposed to use the key for SQL Server, or SBS?

Thanks in advance

Reporting Services is part of the SQL Server license. You should use a SQL Server product key.

Catching Primary Key Violation on insert error

I've read a few different articticles wrt how the handle this error gracefully.

I am thinking of wrapping my sql insert statement in a try catch and have the catch be something like

IF ( e.ToString() LIKE '%System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_PS_HR_Hrs'. Cannot insert duplicate key in object %')

{
lable1.text = "Sorry, you already have reported hours for that day, please select anothe rdate"

}

Is there a better way?

TIA

Dan

Find out the error number that your Exception is throwing, then trap for that particular error number. Your error might be number 2627 or 2601.

|||

Bummer-

I'm away from my asp.net enviormentt.

Um...

how do I do it?

int errorcode = ex.ToInt

if (errorcode = 123) then...

TIA

Dan

(Can't stop thinknkng about this stuff, I think I need professional help!)

|||

Catch the SqlException first, then display your message based on the returned number.

try{}catch (SqlException ex){if (ex.Number.Equals(2627)){// Display your error here}}

|||

Thanks Ed!

(Cute Kid!)