You should not use the Sitecore API to connect to non-Sitecore databases. They are not designed to do that.
Use EF or any other way you prefer - anything that you would use in a project that has no Sitecore.
The connectionstring can stay in the connectionstrings.config where you have it now.
Not sure what the exact issue is, but as you mention you are new to Azure you might be missing some basic information.
Including tables in the Sitecore databases is not a good idea. Let that be clear.
You can however easily create a database for your custom data in the same SQL server instance that is used by the Sitecore databases. There are tutorials on ...
It is very easy to do with Entity Framework:
1) Add CustomDb in connectionstrings.config (as you did)
2) Install Entity Framework nuget package in your project:
3) Create new DbContext by providing connections string name and map your database tables to models:
public class DatabaseContext : DbContext
public DatabaseContext() : base(...
If you are using custom database, treat this as a normal ASP.Net MVC/core application and write the code accordingly.
You can add your connection string inside the web.config and use normal C# code to connect the database.
You can use SqlConnection class to connect database.
Using the SQL management studio you will need to connect to the database server, and then run the below command to modify the password for DB users to one you specified in connectionstring.config. You will need to run this for all DB users specified in the connectionstring.config file (following is an example for "securityuser").
ALTER USER securityuser ...
To fix that you can do the following:
Login to SMSS with your SA
Navigate to the security/user as shown in the image below
Double click on the desired username
Go to the General Tab and modify the password to match the one provided with the one from the connectionstring (as shown below)
Hope this helps!
It is recommended to handle custom DB in different ways instead of using Sitecore APIs. However, need may arise to use dbs (other than default Sitecore Dbs) e.g. for having different publishing targets etc. In that case, after adding the DB in connection string, you also need to add database to your configurations as below -
<?xml version="1.0" encoding=...