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

asked 16 Oct, 08:36

rmgdc77's gravatar image

rmgdc77
41246
accept rate: 0%

2

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

(16 Oct, 09:31) Volker Barth
Replies hidden
1

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.

(16 Oct, 10:01) Breck Carter

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

(16 Oct, 10:38) rmgdc77

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

(16 Oct, 10:44) rmgdc77

Yes,they are with TLS.

(16 Oct, 12:09) Volker Barth

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...

(16 Oct, 12:19) Volker Barth
Replies hidden

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

(16 Oct, 12:39) rmgdc77

I think you could check that via a Login Procedure.

Addionnalky, you might use client certificates with TLS.

(16 Oct, 12:49) Volker Barth

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

(16 Oct, 12:56) rmgdc77
Replies hidden

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.

(16 Oct, 14:32) Volker Barth
showing 5 of 10 show all flat view

Just as a general comment:

  • As you have stated in one comment, you are not talking about database encryption but communication encryption via TLS. But please note that communication encryption via TLS with server certificates is no means to authenticate users, therefore it's also no means to restrict access. It's just a means to prevent others from "reading and understanding" the network traffic between client and server. - It's just the same intention as using HTTPS with a ordinary web server.

  • SQL Anywhere's user management is database-specific, not server-specific (say, in contrast to MS SQL Server or SAP ASE), so in that respect it does not matter whether different databases run on the same or on different servers. Each user will need to have valid credentials to the database he/she wants to access. Of course, if both databases db1 and db2 have credentials for a user "JohnDoe" with the same password, that user could connect to both databases.

  • For that reason, I do not quite understand your thoughts about sharing vs. not sharing "DBKEY" - a DBKEY is used for database encryption, which you do not plan to use according to your comment. So are you talking about the trusted certificates one has to accept? Those are no "secret", so there's not much sense in "hiding" them. For ease of usage, you can store certificates within the database, so there's no need for client users to "mange them". - As you can see from the sample connection strings for TLS clients from the docs, there's no key here to be provided.

permanent link

answered 18 Oct, 02:31

Volker%20Barth's gravatar image

Volker Barth
37.8k347515785
accept rate: 34%

edited 18 Oct, 03:43

Hello Volker & Breck,

Thanks for all the inputs. Altought they were general (as the question was) they will help me to plan the infrastruture / service I need to provide my clients.

Thanks again, Rui Cruz

(19 Oct, 07:34) rmgdc77
Replies hidden

Good to hear that!

Feel free to ask, in case further – general or particular – questions come up.

(19 Oct, 08:29) Volker Barth

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/3bca6a426c5f10148cdfcf381c82255d.html?q=sandbox

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

permanent link

answered 20 Oct, 08:49

Justin%20Willey's gravatar image

Justin Willey
7.5k132175245
accept rate: 19%

edited 20 Oct, 08:49

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.)

(20 Oct, 09:16) Volker Barth

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.

(20 Oct, 09:29) Breck Carter
Replies hidden

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.

(20 Oct, 10:02) Justin Willey
2

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

(20 Oct, 10:43) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×45
×13

question asked: 16 Oct, 08:36

question was seen: 248 times

last updated: 20 Oct, 10:44