2K5 Management Studio and VSS

We are using the Soltution functionality in Management Studio to add and
manage database objects in Visual SourceSafe. The functionality is a great
improvement over that avialable with 2K. However, there is one issue that is
driving us nuts. When you add a project to VSS it creates the actual project
files in a set of nested folders for example, views\views\views. How can we
modify the settings so that it will create the project files in a top level
folder (views)? We have tried editing the project files in VSS and tried
rebinding and sometimes it works and other times it fails. There must be an
ini file or some way to change this behavior.
Regards,
Hello,
If you try to add the files to the solution as links, all the files will be
under this root folder.
Hope this helps.
Thanks
"95Tribe" wrote:

> We are using the Soltution functionality in Management Studio to add and
> manage database objects in Visual SourceSafe. The functionality is a great
> improvement over that avialable with 2K. However, there is one issue that is
> driving us nuts. When you add a project to VSS it creates the actual project
> files in a set of nested folders for example, views\views\views. How can we
> modify the settings so that it will create the project files in a top level
> folder (views)? We have tried editing the project files in VSS and tried
> rebinding and sometimes it works and other times it fails. There must be an
> ini file or some way to change this behavior.
> Regards,

2005: Script objects to separate files?

In addition to many of the other useful things in EM, i cannot find (and i
assume has been removed) the ability to script database objects each to
their own file. e.g.
*.tab
*.viw
*.prc
*.trg
etc
Is this also gone?
Yes. As an alternative, check the blog on this issue and a
utility that provides some missing scripting in SQL 2005:
http://weblogs.sqlteam.com/billg/arc...1/22/8414.aspx
-Sue
On Sun, 26 Mar 2006 13:58:51 -0500, "Ian Boyd"
<admin@.SWIFTPA.NET> wrote:

>In addition to many of the other useful things in EM, i cannot find (and i
>assume has been removed) the ability to script database objects each to
>their own file. e.g.
> *.tab
> *.viw
> *.prc
> *.trg
>etc
>Is this also gone?
>

2005: Script objects to separate files?

In addition to many of the other useful things in EM, i cannot find (and i
assume has been removed) the ability to script database objects each to
their own file. e.g.
*.tab
*.viw
*.prc
*.trg
etc
Is this also gone?Yes. As an alternative, check the blog on this issue and a
utility that provides some missing scripting in SQL 2005:
http://weblogs.sqlteam.com/billg/archive/2005/11/22/8414.aspx
-Sue
On Sun, 26 Mar 2006 13:58:51 -0500, "Ian Boyd"
<admin@.SWIFTPA.NET> wrote:
>In addition to many of the other useful things in EM, i cannot find (and i
>assume has been removed) the ability to script database objects each to
>their own file. e.g.
> *.tab
> *.viw
> *.prc
> *.trg
>etc
>Is this also gone?
>

2005: Script objects to separate files?

In addition to many of the other useful things in EM, i cannot find (and i
assume has been removed) the ability to script database objects each to
their own file. e.g.
*.tab
*.viw
*.prc
*.trg
etc
Is this also gone?Yes. As an alternative, check the blog on this issue and a
utility that provides some missing scripting in SQL 2005:
http://weblogs.sqlteam.com/billg/ar...11/22/8414.aspx
-Sue
On Sun, 26 Mar 2006 13:58:51 -0500, "Ian Boyd"
<admin@.SWIFTPA.NET> wrote:

>In addition to many of the other useful things in EM, i cannot find (and i
>assume has been removed) the ability to script database objects each to
>their own file. e.g.
> *.tab
> *.viw
> *.prc
> *.trg
>etc
>Is this also gone?
>

2005 encryption - symmetric keys

Hi There

We have been playing around with encryption in 2005. I cannot find a BOL topic that discusses dropping encryption objects such as keys.

We do the followign steps:

Create master key with password, then we create a certificate using the master key, we then create a symmetric key using this certificate and encrypt data columns.

But what i find worrying is that you can then drop the symmetric key , there are no warnings that you have objects dependant on this key for decryption.

Once you have dropped the key you cannot decrypt the data anymore?

Also the key defults the expiration date to 1 year.

WHat happens after 1 year when you have encrypted data and an expired key, or someone drops the key ? How can you ever decrypt the data after that ?

You can backup master keys nd certificates but not symmetric keys?

It seems to be that youc an very easily orphan encrypted data by the loss of the symmetric key for whatever reason, is this correct ?

Thanx

In SQL Server 2005 we have support to explicitly encrypt/decrypt data using builtins, but there is no way to mark a column as being used to store encrypted data, therefore there is no way to bound any key to the data encrypted by it. Unfortunately this means that the system doesn’t know if any given symmetric key is being used or not, and they can be accidentally dropped.

As you mentioned, you can create backups for master key and certificates, but in SQL Server 2005 there is no support for backing up individual symmetric keys. To prevent symmetric key loss you can create a database backup or alternatively create your symmetric keys using the KEY_SOURCE and IDENTITY_VALUE syntax. This syntax will allow you to recreate the keys in case they are dropped by mistake, if you don’t want to permanently store the key in the database or if you need to create the same symmetric key in a different database. Example:

CREATE SYMMETRIC KEY key_demo2 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE cert_demo2

go

-- Create a symmetric key with KEY_SOURCE and IDENTITY_VALUE so it can be recreated

CREATE SYMMETRIC KEY key_demo_recreate WITH ALGORITHM = TRIPLE_DES,

KEY_SOURCE = 'Key source - keep this source secret protected!',

IDENTITY_VALUE = 'My Key identity'

ENCRYPTION BY CERTIFICATE cert_demo

go

-- Create a table to store some encrypted data

CREATE TABLE t_secret_data( mySecret varbinary( 100 ) )

go

OPEN SYMMETRIC KEY key_demo_recreate DECRYPTION BY CERTIFICATE cert_demo

go

INSERT INTO t_secret_data values( encryptbykey( key_guid( 'key_demo_recreate' ), 'Secret data 1' ))

INSERT INTO t_secret_data values( encryptbykey( key_guid( 'key_demo_recreate' ), 'Secret data 2' ))

INSERT INTO t_secret_data values( encryptbykey( key_guid( 'key_demo_recreate' ), 'Secret data 3' ))

go

SELECT convert( varchar(100), decryptbykey( mySecret)) FROM t_secret_data

go

CLOSE SYMMETRIC KEY key_demo_recreate

go

-- We can drop the key, either by mistake or on purpose

DROP SYMMETRIC KEY key_demo_recreate

go

-- At this point we cannnot recover the encrypted data.

-- We need to recreate the symmetric key to recover it.

-- notice that the key name is not necessarily the same

CREATE SYMMETRIC KEY key_demo_recreate2 WITH ALGORITHM = TRIPLE_DES,

KEY_SOURCE = 'Key source - keep this source secret protected!',

IDENTITY_VALUE = 'My Key identity'

ENCRYPTION BY CERTIFICATE cert_demo

go

-- Notice that we use the new key name

OPEN SYMMETRIC KEY key_demo_recreate2 DECRYPTION BY CERTIFICATE cert_demo

go

-- Same select statement

-- As the key ring uses the key identifier (key_guid),

-- and not the key name we don't need to change the decryption statement

SELECT convert( varchar(100), decryptbykey( mySecret)) FROM t_secret_data

go

CLOSE SYMMETRIC KEY key_demo_recreate2

go

The certificate expiration date is stored in metadata so it can be used/enforced by any application, but it won’t be used by SQL Server for any of the encryption features. You will be able to encrypt/decrypt data and keys using an expired certificate, but be aware that other SQL Server features such as Service Broker will enforce this expiration date.

I hope this information was able to answer your questions, please feel free to send us follow up questions and additional feedback.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul

That does help alot, exactly what i wanted to know did you get this from BOL, i had trouble searching for a BOL topic with this information, if so do youhave the name of the topic for me ?

Thanx

|||

I am part of the test team for this particular feature and I am quite familiar with it; I really didn’t look for this information on BOL; but I think you are right, we don’t have an article describing symmetric keys and potential data loss in case of an accidental DROP SYMMETRIC KEY in detail. I will open a bug for BOL.

In the meantime, I recommend the following resources:

* CREATE SYMMETRIC KEY (Transact-SQL): http://msdn2.microsoft.com/en-us/library/ms188357.aspx

* DROP SYMMETRIC KEY (Transact-SQL) : http://msdn2.microsoft.com/en-us/library/ms182698.aspx

* How to: Create Identical Symmetric Keys on Two Servers: http://msdn2.microsoft.com/en-us/library/ms366281.aspx

* How to: Encrypt a Column of Data : http://msdn2.microsoft.com/en-us/library/ms179331.aspx

* Laurentiu Cristofor's blog : http://blogs.msdn.com/lcris/

* Raul Garcia’s blog: http://blogs.msdn.com/raulga/

Thanks a lot for the feedback, and feel free to send us more questions and feedback. We really appreciate it.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Excellent, thanx a million Raul.

Yes a BOL topic that discussed dependancies of encryption objects, as well as considerations when dropping Keys etc would definately be valuable, thanx for the feedback.

Cheers

2005 Copy database objects from one server to another

I just upgraded to SQLServer 2005 Standard. I do development in a database
on one server. When a solution is completely tested, I move it to the
operational database on a different server. With SQLServer 2000, I could
just open the .adp file and do File->Get External Data->Import to move the
objects. That does not work with 2005.
What is the recommended method for moving objects from one database to
another? Obviously, the objects that changed will not be the same every time.
Thanks!
Jer
Jerry
You can script out the objects defenition and run it on destination server.
You may want to keep your object's version on VSS to take over deployment
"JerryWendell" <JerryWendell@.discussions.microsoft.com> wrote in message
news:53AA0759-AEEE-4EA9-AAFE-87902B11A8E8@.microsoft.com...
>I just upgraded to SQLServer 2005 Standard. I do development in a database
> on one server. When a solution is completely tested, I move it to the
> operational database on a different server. With SQLServer 2000, I could
> just open the .adp file and do File->Get External Data->Import to move the
> objects. That does not work with 2005.
> What is the recommended method for moving objects from one database to
> another? Obviously, the objects that changed will not be the same every
> time.
> Thanks!
> Jer

2005 Copy database objects from one server to another

I just upgraded to SQLServer 2005 Standard. I do development in a database
on one server. When a solution is completely tested, I move it to the
operational database on a different server. With SQLServer 2000, I could
just open the .adp file and do File->Get External Data->Import to move the
objects. That does not work with 2005.
What is the recommended method for moving objects from one database to
another? Obviously, the objects that changed will not be the same every tim
e.
Thanks!
JerJerry
You can script out the objects defenition and run it on destination server.
You may want to keep your object's version on VSS to take over deployment
"JerryWendell" <JerryWendell@.discussions.microsoft.com> wrote in message
news:53AA0759-AEEE-4EA9-AAFE-87902B11A8E8@.microsoft.com...
>I just upgraded to SQLServer 2005 Standard. I do development in a database
> on one server. When a solution is completely tested, I move it to the
> operational database on a different server. With SQLServer 2000, I could
> just open the .adp file and do File->Get External Data->Import to move the
> objects. That does not work with 2005.
> What is the recommended method for moving objects from one database to
> another? Obviously, the objects that changed will not be the same every
> time.
> Thanks!
> Jer

2005 Copy database objects from one server to another

I just upgraded to SQLServer 2005 Standard. I do development in a
database on one server. When a solution is completely tested, I move
it to the operational database on a different server. With SQLServer
2000, I could just open the .adp file and do File->Get External
Data->Import to move the objects. That does not work with 2005.
What is the recommended method for moving objects from one database to
another? Obviously, the objects that changed will not be the same
every time.
Thanks!
JerJer (jerry.w.black@.saic.com) writes:

Quote:

Originally Posted by

I just upgraded to SQLServer 2005 Standard. I do development in a
database on one server. When a solution is completely tested, I move
it to the operational database on a different server. With SQLServer
2000, I could just open the .adp file and do File->Get External
Data->Import to move the objects. That does not work with 2005.
What is the recommended method for moving objects from one database to
another? Obviously, the objects that changed will not be the same
every time.

The recommended method is to have all SQL objects under version control
and build all install kits from the version-control system.

At http://www.red-gate.com you can find SQL Compare, which many people
use for this sort of thing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Jer wrote:

Quote:

Originally Posted by

I just upgraded to SQLServer 2005 Standard. I do development in a
database on one server. When a solution is completely tested, I move
it to the operational database on a different server. With SQLServer
2000, I could just open the .adp file and do File->Get External
Data->Import to move the objects. That does not work with 2005.
What is the recommended method for moving objects from one database to
another? Obviously, the objects that changed will not be the same
every time.
Thanks!
Jer

A better tool for handling changes via source control is DB Ghost from
Innovartis (http://www.dbghost.com/ggroups) - Red Gate's tools do not
work with source control and thus provide no audit trail whatsoever so
Erland's advice is slightly misleading (sorry Erland, this isn't a
personal attack you just need to understand what Red Gate's tool's
capabilities and limitations are).

Regards,

Malcolm (www.innovartis.co.uk)