Install SAP SQL Anywhere in Cloud

[former_SQLA_member1694945]

Hi All,

We would like to install SAP SQL Anywhere on a server that would allow to serve multiple tenants. I already read some articles posted about the subject and the advantages / disadvantages about each method but I have some worries about each method:

1) Different SQLSRV per tenant - each server would compete for server resources - in terms of security it seems more secure to me (each server would have an encryption key for TDS / ODBC communication) my worries are how would this scale and how the different servers would compete for resources (databases will be small ~2GB each lets say) - in a server with 32GB RAM how much clients could I have and still getting a decent performance?

2) One server with multiple databases - the server would have multiple databases each providing isolation (in terms of storage) - problem is communication layer since all databases will be served in the same PORT and with the same encryption KEY - if an malicious actor with the key (the key must be installed on all tenants / clients so it's relatively easy to access) could guess the database name it would have access to all tenants DB's using only one KEY - the exposure to a breach is wide.

3) In terms of the encryption key instalation on the client side do you think it would be more secure to have the key installed manually (client must download the access key from its client area and store the key on its profile for example) or the key is never stored locally and there is a central system where the app (when logged on) would call a WebService that would download the key do a temporary folder and then use it to connect to the DB.

Sorry for the "too much" questions!

Thanks in advance, Rui Cruz

[VolkerBarth]

Just a note: AFAIK TDS connections are not encrypted when using SQL Anywhere's TLS connection layer.

[Breck_Carter]

Your questions are general in nature, so here are some general answers:

Start with all the *.db files on one dbsrv.exe instance on one computer, to keep administration (including backup and recover) as simple as possible. This gives you a gentle introduction to cloud administration.

When one or more *.db files become much busier than the rest, consider moving it (them) to a separate dbsrv.exe instance on the same computer... or, if one database is 100 times busier than the others, perhaps move it to a dbsrv.exe running on a separate computer. Administration is a bit more difficult, but it's still easy for the majority of databases.

IMO encryption keys aren't like school bus keys to be kept on a pegboard at the dispatch office... encryption keys are critical to the clients around the clock. The clients are motivated to keep the keys safe, so give them possession and control. Central copies should be kept under lock and key for emergency backup use, but not passed around on a regular basis.

Add rules and protocols as the needs arise... don't invent hypothetical risks that don't apply to your environment. When administration becomes too complex, folks find shortcuts in order to get the work done.

[former_SQLA_member1694945]

Hi Volker. Thanks for your input.

I was refering to (maybe wrongly) to ODBC connections via the encryption=tls connection option!

This is encrypted with TLS right?

Thanks, Rui Cruz

[former_SQLA_member1694945]

Hello Breck,

Thanks for the info. Yes they where general questions because i still don't have an ideia whats the best way to implement the fact i have multiple tenants that don't don't to have infrastruture (servers and IT) and want a cloud solution. I normally force on-permisses but nowadays simple client just don't want to have to manage a server and I would have a comercial solution for this cases.

You refer that the KEYS aren't like BUS KEYS but this kind of clients (its a OEM version of a software) don't have the knowledge to manage the keys secure so I would want to "create" a system that would be easy to administer but with some security.

Your sugestion was to put everything on one server - you don't see this has a problem if the KEY gets compromised (lets say TENENANT A has some malware that gets the KEY and figures out the KEY is related with my server - it would allow to connect to all DB's on my server right?)

I normally would have a DBA common to all tenants since i would be administering this tenants.

Thanks again, Rui Cruz

[VolkerBarth]

Yes,they are with TLS.

[VolkerBarth]

What do you mean with encryption keys — are you relating to database encryption (dbsrv -ek or -ep) or communication encryption (TLS via certificates)? Because these are different concepts and are setup on different levels (database-specific vs. engine-specific).

If you are going to use database encryption, will the client start the databases themselves, or are they started by you? Because in the former case, clients will not need the DBKEY to connect...

[former_SQLA_member1694945]

Hi Volker,

I mean the TLS via certificates - I know the database itself can / should be encrypted via a DBKEY and this is a per / database setting but - if I am correct - communication via TLS uses the same certificate for all databases within one server and all databases share the same certificate.

My problem is the DBA user will be the same for all tenants - it is possible to restrict the DBA connection to local / shared memory connections?

Thanks again, Rui Cruz

[VolkerBarth]

I think you could check that via a Login Procedure.

Addionnalky, you might use client certificates with TLS.

[former_SQLA_member1694945]

Volker,

What do you mean with "you might use client certificates with TLS"? - each client having a different certificate? Can you give me more info about this? Can I achieve communication isolation per TENANT database with this method?

Thanks, Rui Cruz

[VolkerBarth]

What do you mean by communication isolation?

Server certificates, as required by TLS, are used to allow encrypted communication and allow the client to verify the server's identity. In contrast, client certificates can be used with TLS to allow the server to verify the client's identity (at least as long as clients do not share certificates). — Note, this concept is not specific to SQL Anywhere at all.

See here for the topic in the docs.

[justin_willey]

Something else to think about, depending on the architecture you eventually choose, and what features your system provides, is about isolating the databases from each other. Look at both disk sandboxing and the -sf security settings option.

https://help.sap.com/viewer/61ecb3d4d8be4baaa07cc4db0ddb5d0a/17.0/en-US/3bca6a426c5f10148cdfcf381c82...

https://help.sap.com/viewer/61ecb3d4d8be4baaa07cc4db0ddb5d0a/17.0/en-US/3bc96ffa6c5f10149a878e089d92...

[VolkerBarth]

I'd add the -edi database server option to allow "database isolation". (Note, it can also be controlled via the security settings option Justin has suggested.)

[Breck_Carter]

AFAIK disk sandboxing does not affect how database files are isolated from one another... it affects where non-database files are written: relative to the folder containing the database versus relative to the server's working folder. In a cloud environment both of these locations are on the cloud computer, and they imply the end user has file access to that computer. If not, then perhaps the non-database files should be written to the client computer.

[justin_willey]

Yes indeed. I suppose I mean stopping someone who has got access to xp_read_file() etc getting at other peoples' database file & whatever is on the server.

[Breck_Carter]

I would imagine the default configuration for a new multi-client cloud setup should be "allow nothing until proven necessary" 🙂