SQL server creating Digital Certificate

We have an application that uses SQL Server 2005 or above, when we create the database it currently creates various logins we have various stored procedures that are signed with a Certificate.

Currently when the Certificate is generated or backed up it uses the SQL Server data directory this can cause issues with AV software and other server permission problems.

Essentially we need to find a temporary directory we can copy the certificate to for installation purposes, the only way I can get the temp directory is through xp_cmdshell using the following script:-

DECLARE @temp nvarchar(255)
CREATE TABLE #Tmp
(
EnvVar nvarchar(255)
)
INSERT INTO #Tmp exec xp_cmdshell 'echo %TMP%'
SET @temp = (SELECT TOP 1 EnvVar from #Tmp)

SELECT @temp as 'Windows Directory'

drop table #Tmp

However this is no 开发者_开发百科good if the xp_cmshell component is switched off in SQL Server.

Does anyone have any other suggestions, longer term we are going to ditch the digital certificate but in the short term we cannot do this.

How about a CLR Stored procedure that outputs Environment.GetFolderPath(SpecialFolder.Temp);

This requires you deploy an assembly and that the server is configured to allow use of the CLR.