Thread: SSL with Access VBA

Hi,
could someone help me build a connectionstring to use in Access VBA to
get a SSL Connection or is there an HowTo?

Client is Access 2013 with psqlODBC 9.5.400.
Server PostgreSQL 9.5 on UBUNTU 16.04.1 with the standard installation.
postgresql.conf has SSL=ON so I expect it to work?   :}

pgAdmin connects just fine with "SSL = required".

This String works with VBA ADODB.Connection without SSL:
strCon = "DRIVER={PostgreSQL

UNICODE};DATABASE=db_test;SERVER=192.168.1.1;PORT=5432;UID=me;PWD=x;A1=7.4+;B1=65535;A7=50;B9=0;C6=1;A4=1;C5=1;C4=1;C8=1;B6=1;UniqueIndex=1;B7=1;AB=0x2"

BTW is this still OK? I used this for years.

When I add ";sslmode=require" connection.open fails with:
-2147467259   server does not support SSL, but SSL was required

In the parameter-list there is CA as abrevation for sslmode
There is no further information what values are needed.
Adding ";CA=1" connects to the server but how would I know if it is
encrypted?


Additional question: How can I activate SSL Compression?


Regards
Andreas

On 11/09/2016 10:51 AM, Andreas wrote:
> Hi,
> could someone help me build a connectionstring to use in Access VBA to
> get a SSL Connection or is there an HowTo?
>
> Client is Access 2013 with psqlODBC 9.5.400.
> Server PostgreSQL 9.5 on UBUNTU 16.04.1 with the standard installation.
> postgresql.conf has SSL=ON so I expect it to work?   :}
>
> pgAdmin connects just fine with "SSL = required".
>
> This String works with VBA ADODB.Connection without SSL:
> strCon = "DRIVER={PostgreSQL
>
UNICODE};DATABASE=db_test;SERVER=192.168.1.1;PORT=5432;UID=me;PWD=x;A1=7.4+;B1=65535;A7=50;B9=0;C6=1;A4=1;C5=1;C4=1;C8=1;B6=1;UniqueIndex=1;B7=1;AB=0x2"
>
>
> BTW is this still OK? I used this for years.
>
> When I add ";sslmode=require" connection.open fails with:
> -2147467259   server does not support SSL, but SSL was required
>
> In the parameter-list there is CA as abrevation for sslmode
> There is no further information what values are needed.
> Adding ";CA=1" connects to the server but how would I know if it is
> encrypted?

What are your settings in pg_hba.conf for connections?

>
>
> Additional question: How can I activate SSL Compression?
>
>
> Regards
> Andreas
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Hello Adrian,

thanks for pointing me to this "md5" in your other answer.
I fear, I found the SSL issue myself.   =8-}

Would you know how to turn on the SSL-compression for ADODB.Connections?
... and for PHP, too?
Is it some option in the connection string?

On the other hand, I found warnings that enabling ssl-compression brings
the danger of exposing oneself to BEAST and CRIME (whatever that might be).


Thanks in advance
Andreas




Am 09.11.2016 um 21:14 schrieb Adrian Klaver:
> On 11/09/2016 10:51 AM, Andreas wrote:
>> Hi,
>> could someone help me build a connectionstring to use in Access VBA to
>> get a SSL Connection or is there an HowTo?
>>
>> Client is Access 2013 with psqlODBC 9.5.400.
>> Server PostgreSQL 9.5 on UBUNTU 16.04.1 with the standard installation.
>> postgresql.conf has SSL=ON so I expect it to work?   :}
>>
>> pgAdmin connects just fine with "SSL = required".
>>
>> This String works with VBA ADODB.Connection without SSL:
>> strCon = "DRIVER={PostgreSQL
>>
UNICODE};DATABASE=db_test;SERVER=192.168.1.1;PORT=5432;UID=me;PWD=x;A1=7.4+;B1=65535;A7=50;B9=0;C6=1;A4=1;C5=1;C4=1;C8=1;B6=1;UniqueIndex=1;B7=1;AB=0x2"
>>
>>
>>
>> BTW is this still OK? I used this for years.
>>
>> When I add ";sslmode=require" connection.open fails with:
>> -2147467259   server does not support SSL, but SSL was required
>>
>> In the parameter-list there is CA as abrevation for sslmode
>> There is no further information what values are needed.
>> Adding ";CA=1" connects to the server but how would I know if it is
>> encrypted?
>
> What are your settings in pg_hba.conf for connections?
>
>>
>>
>> Additional question: How can I activate SSL Compression?
>>
>>
>> Regards
>> Andreas
>>
>>
>
>

On 11/09/2016 04:29 PM, Andreas wrote:
> Hello Adrian,
>
> thanks for pointing me to this "md5" in your other answer.
> I fear, I found the SSL issue myself.   =8-}
>
> Would you know how to turn on the SSL-compression for ADODB.Connections?
> ... and for PHP, too?
> Is it some option in the connection string?

I have no idea, I do not use either. Sorry.

>
> On the other hand, I found warnings that enabling ssl-compression brings
> the danger of exposing oneself to BEAST and CRIME (whatever that might be).
>
>
> Thanks in advance
> Andreas
>
>
>
>



--
Adrian Klaver
adrian.klaver@aklaver.com

Hi,

I've written up instructions for setting up a postgres system on Windows with Access clients, encrypted connections and SSPI authentication.

Although the application itself isn't relevant, these instructions cover configurations required for that kind of setup that may be useful to you.

Probably don't enable ssl compression, it is an attack vector and so may no longer be available in tls1.3 anyway.

You can verify if your connection is encrypted by using PSQL, it'll always print this information on connection. Pgadmin shows that info too.

Server stuff:

https://github.com/lindsay-stevens/openclinica_sqldatamart/blob/master/docs/setup/advanced.md

Client stuff:

https://github.com/lindsay-stevens/openclinica_sqldatamart/blob/master/docs/clients/general.md

Hope it helps