Re: file system permissions and cross-database certificates?

Craig Thomas (CraigThomas@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
Is it really necessary to use the file system to move a certificate from
one database to another within the same instance?

That is, is the use of the file system to BACKUP CERTIFICATE TO
FILE='C:\temp\mycert.cert' in one database, so that CREATE CERTIFICATE
FROM
FILE='C:\temp\mycert.cert' in a second (or subsequent) database the only
way to achieve the key handling that enables certificate based
permissions?

Or is there some internal-to-SQL Server mechanism that I have not yet
found in the books on line?

I have not found anything yet. I agree that it's a bit clumsy to
use the file system.

Then there's the xp_cmdshell firehose that represents the most practical
approach toward allowing a repeatable BACKUP CERTIFICATE command -- if
you don't delete the file first, your command will fail, and the most
reasonable way to deal with this is to script the delete. But either you
use a windows shell with file-system permissions or you open up
xp_cmdshell to a SQL Server user.

There is xp_delete_file, but it's undocumented and unsupported.

You could write a CLR procedure to do the delete, then you do not at least
need to enable xp_cmdshell. Then again, the database is likely to have
be marked as trustworthy.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: why>?
    ... of ADP and OLAP just as you'd use them with SQL Server? ... Database as file system ain't new. ... talk of WinFS by years. ... Yup, database as storage subsystem. ...
    (microsoft.public.excel)
  • Re: drawbacks of Saving documents to Sql server
    ... Retrieving the files from the file system is always faster but not necessarily better. ... In the database I used a table to store a "header" record with one row per file containing the filename, filesize, number of 64K chunks, etc... ... SQL Server 2005 performs better than SQL Server 2000 hands down. ... that microsft is gong more towars saving everthing in sql server. ...
    (microsoft.public.dotnet.framework)
  • Re: drawbacks of Saving documents to Sql server
    ... by anychance know or have the code to retrive the documents from the database ... retriving documents, do you think u might be able to point me to online ... Writing random documents to SQL was slower than the file system. ... SQL Server 2005 performs better than SQL Server 2000 hands down. ...
    (microsoft.public.dotnet.framework)
  • Re: drawbacks of Saving documents to Sql server
    ... by anychance know or have the code to retrive the documents from the database ... retriving documents, do you think u might be able to point me to online ... Writing random documents to SQL was slower than the file system. ... SQL Server 2005 performs better than SQL Server 2000 hands down. ...
    (microsoft.public.dotnet.framework)
  • Re: MS Access DAO -> ADO.NET Migration
    ... William Vaughn ... Microsoft MVP ... Hitchhiker's Guide to Visual Studio and SQL Server ... My migration app works building a SSCE database file with imported data ...
    (microsoft.public.dotnet.framework.adonet)