Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (2)

Resolved SQL Server Encryption

  1. How-to
0
Votes
Undo
  1. Roland Smith
    Roland Smith
  2. PowerBuilder
  3. Friday, 15 February 2019 19:34 PM UTC

We want to give our customers the option to use database encryption in SQL Server.

We are currently using PB12.5 and the SNC driver which has the Encrypt dbparm.

Other than setting the Encrypt dbparm, what else needs to be done at the client? Do we need to install a certificate on the client or just the server?

What happens if the app attempts to connect to an encrypted database without the encrypt dbparm? What happens if the encrypt dbparm is set but the database is not encrypted? I tried it but got an error message about invalid certificate.

 

QR Code as bitmap in Datawindow
Encrypt in MD5
Responses (2)
mike S
mike S Accepted Answer Pending Moderation
  1. Saturday, 16 February 2019 15:43 PM UTC
  2. PowerBuilder
  3. # 1

cert is at the server.  If you have an sql azure account, you can see how it works without having to setup a  server.

this is an example connection for oledb from powerbuilder to sql azure:

SQLCA.DBParm = "PROVIDER= 'SQLOLEDB',DATASOURCE='yourazuresqlname.database.windows.net', PROVIDERSTRING='database=yourdatabase;encrypt=TRUE',CommitOnDisconnect='No',DelimitIdentifier='No', OJSyntax='ANSI'"

 

there are settings on the server if you want to require encryption.

if you don't install a cert, i think sql server generates one for itself automatically.  you can setup the client to trust it (off by default) even though it isn't signed.  these are other options you should set if you are using self signing cert:

https://docs.microsoft.com/en-us/sql/relational-databases/native-client/features/using-encryption-without-validation?view=sql-server-2017

 

Comment
There are no comments made yet.
David Peace (Powersoft)
David Peace (Powersoft) Accepted Answer Pending Moderation
  1. Monday, 18 February 2019 15:31 PM UTC
  2. PowerBuilder
  3. # 2

Hi Roland

Have you proven that you can connect using just the MS tools? In my limited knowledge you will have to install a certificate at the server end and then the client needs to connect with encryption. It will get the certificate and if it has any query that will need to be resolved, like a browser accessing a site with a poor certificate.

Sorry its not a massive help, but I would build this up in layers before introducing PB into the equation.

Cheers

David

Comment
David Peace (Powersoft)
  1. David Peace (Powersoft)
  2. Monday, 18 February 2019 15:37 PM UTC
I forgot to say that you can install the certificate on the client in order to establish the trust relationship. According to my support guy, it's all at the transport layer so once you have that working it will work with PB.



:0)
  1. Helpful
Roland Smith
  1. Roland Smith
  2. Monday, 18 February 2019 16:04 PM UTC
We have a server with a certificate installed. I found that if I use the Encrypt dbparm and use the full server name, it connects. I was wondering if there is a error number I could watch out for if Encrypt is on for a server that doesn't have encryption turned on or not turned on for a server with encryption.
  1. Helpful
David Peace (Powersoft)
  1. David Peace (Powersoft)
  2. Tuesday, 19 February 2019 09:25 AM UTC
I'm sorry, I assumed you were having problems connecting.... should have read your question. :)



I do not know the answer to that question.



Cheers

David
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.