Two SSL certificates with same key

Question

I am looking at whether it is possible to execute arbitrary SQL commands (dynamic SQL statements or stored procedures) and return the result as XML, in an Azure SQL Database.

I know it can be done with regular, on premises SQL Server database - in our case we use a CLR function. Alternatives 'regular' solutions include using OPENROWSET or OPENQUERY, neither of which is available for Azure.

A new EXECUTE ... AS FOR XML option is available as of SQL Server 2012, but when I try it I get an error - I am unable to locate correct examples of its use.

exec ('select ''A'', 2, ''d''')
with result sets (as for xml)

returns

Msg 11537, Level 16, State 1, Line 1 EXECUTE statement failed because its WITH RESULT SETS clause specified 1 column(s) for result set number 1, but the statement sent 3 column(s) at run time.

To be explicitly clear; I can't control the command being passed - it is most likely a stored procedure, and it most likely returns a single 'regular' (i.e. non-xml) resultset. The use case for this is in a set of SQL tests, not the actual production code.

Solution 1

Re-using the same private key to generate a new certificate request technically doesn't increase your risk exposure. Essentially the same public key is being used, but the x509 headers are changed with the updates site name, dates, or whatever other changes there are between the two certificates.

Stepping back, you should analyze the reason for not regenerating the private key when renewing a certificate/requesting a new certificate. Although current encryption algorithms cannot be broken, then longer you use the same private key, the more information is encrypted with that key, and the larger the pool of data will be for any future crypto-analysis. (Although in reality a session key encryption a majority of the data sent between clients, there still is technically more sessions being security with the same public/private keypair, so the same principle is applies).

As a general practice, it is best to generate a new private key when certificates are up for renewal.

Solution 2

Short answer is no.

The only case visible to me - if somebody can steal private part of one cert or factorize it, he will be able to decipher both sites.

Comments

[pboin]

Just curious: why would you go out of your way to do this?

[yankeemike]

As SteveM guessed, i'm verifying if i should use the same key generating 2 csr files for two separate certificates.

[Herve Roggero]

I cannot confirm the following right now, but here is a thought: SQL Database runs in backward compatibility mode; the databases in Azure are set to SQL 2008 R2 compatibility - this might explain why it works on a local SQL 2012 machine but not in Azure.

[Oskar Austegard]

Herve - actually the DB version of SQL Azure is Microsoft SQL Azure (RTM) - 11.0.1944.0 May 24 2012 03:22:34 - which is a SQL 2012 version. And if my select above had only a single column it would have worked - the proper answer is that my understanding of AS FOR XML was not correct - see Steve Howard's statement in the accepted answer below.

[Herve Roggero]

Oskar - I wasn't referring to the version of the SQL engine, which is indeed SQL 2012. I am referring to the compatibility mode of the database. All you have to do is run this statement and you will see that the databases in Azure are actually running in mode 100, which is SQL 2008: SELECT compatibility_level, name FROM sys.databases - here is the article that discusses compatibility mode: msdn.microsoft.com/en-us/library/bb933794.aspx

[Oskar Austegard]

@Herve - huh - I was not aware of that, thanks for the info!

[Oskar Austegard]

Sorry, I don't quite follow - where do you put the FOR XML AUTO TYPE statement? Keep in mind that the 'select ...' statement above is unknown to me at execution time, and that it would more likely be a stored procedure to be executed.

[Oskar Austegard]

Lynn, the problem isn't just returning XML from a SELECT stament that I can edit - the problem is return ing XML from an EXEC statement. SQL 2012 has a new AS FOR XML option but documentation is horribly difficult to come by other than msdn.microsoft.com/en-us/library/ms188332.aspx

[Oskar Austegard]

Lynn, thanks for continuing to look at this, but again - the original stored procedure (over which I have no control) does not return XML. The point of the question is how to use the AS FOR XML option of EXECUTE in SQL 2012. Your code is showing the traditional FOR XML option on a SELECT.