NYCPHP Meetup

[Gary Mort]

Bill P. wrote:
> Hello,
>
> I have a project that will be using IIS and mssql.
> I need to use an encrypted conneciton string in my config/lib.php file.
>
> mssql has built in encryption connection ability, but within asp.
>
> Everything will be written in php so does anyone have any suggestions 
> on encrypting a connection string to connect to a mssql db?
>
What's the point of encrypting the data?
Reading through 
http://msdn2.microsoft.com/en-us/library/ms998280.aspx
and
http://msdn2.microsoft.com/en-us/library/ms998283.aspx, it is purely a 
local system server protection.

IE
You have web servers A. B. and C
You have MSSQL Server D and a database(MyDatabase)

A web application running on the WEB server wants to connect to the 
MSSQL server.
Using ASP,. some code somewhere says "Connect to MyDatabase, a MSSQL 
Database "

ASP will then search for a text file called web.conf, and it will grab 
the connection information stored there(the server it is on, the 
database name, yadda yadda).

Than it connects up to the server and does whatever you want it to do.

"Encrypted connection string" just means that the data stored in that 
text file is encrypted, using either an RSA mechanism or DPAPI - which 
to tell you the truth is completely beyond me.  I know there are 
different mechanisms for encryption.  I know in a general way how 
public/private key encryption works. And I know the longer the key 
length, in general, the more secure and the longer to process.  When I 
need to know more than that, I research what the industry stds are at 
that time, what is the best performance for a situation, and move on.  
Trying to understand the math behind it all would drive me bonkers. 

Using encrypted connection strings, the ASP program has an extra step. 
It accesses the text file, grabs the data, and then decrypts the data 
and uses it.  It doesn't actually send the data in an encrypted format 
to the server - the key to noticing this for me was that your ASP 
program can display the plaintext decoded info to you, so it's decoding 
it on the web server. 

So really, you get the same benefit by choosing any encryption scheme 
you like for PHP and encrypting the connection information file that 
your using.

The only reason I can see to use "encrypted connection strings" the 
Microsoft way is the method used in that RSA article(link above).

There, they suggest that for a server farm, what you would do is create 
the connection string file on the MSSQL server system(or on your own 
system, whatever.  The point is, you don't create it on servers A,B, and 
C).  Than you encrypt the file using RSA and transfer both the encrypted 
file AND the RSA private key needed to decrypt the file to servers 
A,B,and C.  Than by installing them under the appropriate user, web apps 
running under that user can decrypt the data.

If that is your goal, find a PHP RSA decryption routine, place the 
private key somewhere secure on your server, and use the key to decrypt 
the connection string file.  Than parse the file and shove the 
appropriate fields into your PHP connection.  If you want to use their 
method, they store the private key in the registry, so you can pull it 
out the same way.

Am I missing something here on how encrypted connection string works?  
(Besides the obvious that Microsoft went to great lengths to describe a 
fairly simple process in complex language such that it would appear to 
be a function exclusive to their API, instead of a fairly trivial 
implementation of standard config file encryption.  I mean, on first 
glance, I thought it was pretty cool, the concept being that you would 
provide the web servers with a connection string encrypted just for each 
server, and that server would send the encrypted data to the database 
server - thus it would not ever know what the true userid and password 
was and since the key is assigned to an ip address, a connection using 
the same config file from a different system would be rejected.  But no, 
it doesn't seem to be that, just a local config file encryption.)