BACKUP CERTIFICATE alters ACLs on created file

NOONE ASKED FOR THIS!!! why the heck is BACKUP CERTIFICATE altering the file's ACLs - it's just a PUBLIC CERT I'm exporting!!

in additions MS doesn't seem able to add the ability to re-inherit file-rights from parent objects via the command-shell.

I export the public-key for a reason! I want to import it on another machine (automatically) to establish a trust. But this freaking "feature" is costing me HOURS of research on how to get rid of these ACLs on a public certificate!!!

argh!!!

sorry, I really had to get this out!

why not at least give us a switch we can set in BACKUP CERTIFICATE command to stop this from happening?

is there a work-around? now I need to activate
EXEC sp_configure 'show advanced options', 1
and
EXEC sp_configure 'xp_cmdshell', 1
just to be able to issue a (not working) DOS command cacls... to try to fix the "feature".

there's got to be a better way!!!

also - why can't there be a switch to force overwriting the target file, if it already exists? It's a BIG PAIN having to do lots of other stuff, just to be able to "backup" the certificate!

You gave us "xp_FileExist", but no xp_FileDelete with similar parameters!! again, I need to issue DOS COMMANDS just to do stuff I would not have to if someone at the levers was actually activating the back-brain!

PLEASE give me some hints on how MS recommends to solve these features!

Hello -

First, you probably really don't want a lot of OS features in a database. Every one of them represents an extreme security risk. The ability to delete files from SQL Server would be a very dangerous thing, while checking to see if a file is present is less of a danger. There are always tradeoffs.

Second, the ACLs are there for protection, not to make things harder. We aren't trying to make things difficult. We do, however, want to hear your concerns - here's a good place for you to voice your wish-list:

http://connect.microsoft.com/SQLServer

Also, you can perform OS operations more safely in a CLR procedure than turning on xp_cmdshell. This article talks a little about the CLR and file ops:

http://aspalliance.com/1081_CLR_Integration_in_SQL_Server_2005

|||

then why supply xp_File* in the first place?

what's the point in securing a public certificate? would it not be reasonable to at least be able to turn that "feature" off by switch? even the private-key is secured by PWD and IIS, or the Certificate-MMC-SnapIn, for instance, leave the ACLs alone as they do assume anyone doing stuff like that knows what he's doing. This goes more so for any DBA exporting certs, as it's not that easy without reading some docs.

but thanks for your input!

I will try some more - maybe I can disable rights-changing on the network-share to get SQL to stop messing around with my ACLs.

|||

The file sp's are there to help - and hopefully the least dangerous ones are all you need to do in a database. I'd recommend doing any other file operations (including the ACL work) in CLR's.

I agree that it would be great to have docs around this. In fact, we change the docs all the time, and add "use cases" that deal with this kind of thing. Again, thanks for using our stuff. We want you to be able to do your job quickly and easily. Go to that site I referenced and sound off! The developers acutally read what you put there and code the product around the features that get the most noise. Not many other software companies work like that, believe me.

|||

Buck Woody - MSFT wrote:

First, you probably really don't want a lot of OS features in a database. Every one of them represents an extreme security risk.

one last note on that: I wouldn't have to, if SQL wouldn't mess-up (or mess around) with the ACLs and if SQL would let me overwrite a certificate. Instead it chooses to be smart and not let me turn off this smartness...

|||Point taken. Seems there are a couple of ways to handle this - please be sure and visit the link I mentioned so that the developers will get your requests!

Backup and restore

Hello to all readers.
I can perform a Backup like this :
Public Sub Backup(ByVal dtCurrentId As DateTime)
Dim sSQLSvrDir As String = GetSqlDataDirectory()
Dim sDataDir As String = sSQLSvrDir & "\Data\"
Dim sServerName As String = Environment.MachineName & "\" & INSTANCE_NAME
Dim sCommand As String
'close the actual connexion
CloseDatabase()
'set the connection string to the the master database
sConnectionString = BuildConnectionString(sServerName, MASTERDB,
String.Empty, SecurityMode.smWindowsIntegrated)
'process the backup
sCommand = "USE master;"
ExecuteSQL(sConnectionString, sCommand)
sCommand = "EXEC sp_addumpdevice 'disk', 'PIPTBackup', '" & g_sAppPath &
BACKUPSUBDIR & "\Backup PIP Traceability " & Format(dtCurrentId, "yyyy-MM-dd
HH-mm-ss") & ".dat';"
ExecuteSQL(sConnectionString, sCommand)
sCommand = "BACKUP DATABASE [PIP Traceability] TO PIPTBackup;"
ExecuteSQL(sConnectionString, sCommand)
sCommand = "EXEC sp_dropdevice 'PIPTBackup';"
ExecuteSQL(sConnectionString, sCommand)
're open the database connection
Call OpenDatabase()
End Sub
This work fine.
But the restore :
Public Sub Restore(ByVal dtCurrentId As DateTime)
Dim sSQLSvrDir As String = GetSqlDataDirectory()
Dim sDataDir As String = sSQLSvrDir & "\Data\"
Dim sServerName As String = Environment.MachineName & "\" & INSTANCE_NAME
Dim sCommand As String
'close the actual connexion
CloseDatabase()
'set the connection string to the the master database
sConnectionString = BuildConnectionString(sServerName, MASTERDB,
String.Empty, SecurityMode.smWindowsIntegrated)
'process the backup
sCommand = "USE master;"
ExecuteSQL(sConnectionString, sCommand)
sCommand = "EXEC sp_addumpdevice 'disk', 'PIPTBackup', '" & g_sAppPath &
BACKUPSUBDIR & "\Backup PIP Traceability " & Format(dtCurrentId, "yyyy-MM-dd
HH-mm-ss") & ".dat';"
ExecuteSQL(sConnectionString, sCommand)
sCommand = "RESTORE DATABASE [PIP Traceability] FROM PIPTBackup; EXEC
sp_dropdevice 'PIPTBackup';"
ExecuteSQL(sConnectionString, sCommand)
sCommand = "EXEC sp_dropdevice 'PIPTBackup';"
ExecuteSQL(sConnectionString, sCommand)
're-open the database connection
Call OpenDatabase()
End Sub
does not work. I get an error message telling that the database is used by
an other user !!! (during the Restore SQL)
Any suggestion will be very appreciated.
Thanks
hi Ouaf,
"Ouaf" <ouaf@.microsoft.com> ha scritto nel messaggio
news:%23rfqPRbkEHA.3896@.TK2MSFTNGP15.phx.gbl...
> does not work. I get an error message telling that the database is used by
> an other user !!! (during the Restore SQL)
> Any suggestion will be very appreciated.
>
it usually depends on Connection Pooling... your connection is closed, but
the connection pooler usualy takes up to 1 minute to completely release
it... so you have to wait a little...
Ado.Net shoul'd expose a "non pooled" property for connections...
but other cons... the connection will neveer be picked up from an existing
pool, so it will take a little more to create it
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Andrea,
do you think that a 'dispose' could help ?
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> a crit dans le message de
news:2prk8bFohgqlU1@.uni-berlin.de...[vbcol=seagreen]
> hi Ouaf,
> "Ouaf" <ouaf@.microsoft.com> ha scritto nel messaggio
> news:%23rfqPRbkEHA.3896@.TK2MSFTNGP15.phx.gbl...
by
> it usually depends on Connection Pooling... your connection is closed,
but
> the connection pooler usualy takes up to 1 minute to completely release
> it... so you have to wait a little...
> Ado.Net shoul'd expose a "non pooled" property for connections...
> but other cons... the connection will neveer be picked up from an existing
> pool, so it will take a little more to create it
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Ouaf,
"Ouaf" <ouaf@.microsoft.com> ha scritto nel messaggio
news:%23ReD5jdkEHA.3608@.TK2MSFTNGP09.phx.gbl...
> Thanks Andrea,
> do you think that a 'dispose' could help ?
really do not know... but I do not think so... :-(
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
OK, that's working. I have to wait for a while for the connection release
from the pool.
(In the dev env, I had also to unsucsribe the database from the server
browser :-) )
Is there a cool way to test that the connection has been released from the
pool ot do I have to catch the error when trying the restore ?
Thanks
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> a crit dans le message de
news:2prk8bFohgqlU1@.uni-berlin.de...[vbcol=seagreen]
> hi Ouaf,
> "Ouaf" <ouaf@.microsoft.com> ha scritto nel messaggio
> news:%23rfqPRbkEHA.3896@.TK2MSFTNGP15.phx.gbl...
by
> it usually depends on Connection Pooling... your connection is closed,
but
> the connection pooler usualy takes up to 1 minute to completely release
> it... so you have to wait a little...
> Ado.Net shoul'd expose a "non pooled" property for connections...
> but other cons... the connection will neveer be picked up from an existing
> pool, so it will take a little more to create it
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Ouaf,
"Ouaf" <ouaf@.microsoft.com> ha scritto nel messaggio
news:u7d46oykEHA.3520@.tk2msftngp13.phx.gbl...
> Hi Andrea,
> OK, that's working. I have to wait for a while for the connection release
> from the pool.
> (In the dev env, I had also to unsucsribe the database from the server
> browser :-) )
> Is there a cool way to test that the connection has been released from the
> pool ot do I have to catch the error when trying the restore ?
I think you always have to catch the exception(s) =;-DDDD
you can perhaps set the "autoclose" database property to help you this way
(default setting on MSDE platform), but beware that this setting has access
penalties as it always close the database when no active connections are
present, and reopen it as new connections are live..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea & Ouaf,
It does expose such a thing. You can set "Pooling=false" in your connection
string and pooling will be disabled. That's the setting for SqlClient. For
OleDB client, it's "OleDBServices=-4".
I'd suggest avoiding AutoClose. I've seen diabolical problems caused by it,
many more than it was intended to solve, plus it won't really help here.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2prk8bFohgqlU1@.uni-berlin.de...[vbcol=seagreen]
> hi Ouaf,
> "Ouaf" <ouaf@.microsoft.com> ha scritto nel messaggio
> news:%23rfqPRbkEHA.3896@.TK2MSFTNGP15.phx.gbl...
by
> it usually depends on Connection Pooling... your connection is closed,
but
> the connection pooler usualy takes up to 1 minute to completely release
> it... so you have to wait a little...
> Ado.Net shoul'd expose a "non pooled" property for connections...
> but other cons... the connection will neveer be picked up from an existing
> pool, so it will take a little more to create it
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||"Greg Low [MVP]" <greglow@.lowell.com.au> ha scritto nel messaggio
news:e5ix4$5kEHA.3016@.tk2msftngp13.phx.gbl...
> I'd suggest avoiding AutoClose. I've seen diabolical problems caused by
it,
> many more than it was intended to solve, plus it won't really help here.
>
=;-D
thank's Greg
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply