Reading data from production database

Question

User352354000 posted

I have been using Microsoft sync framework to sync data b/w two locations and that sync framework is not supported by micro soft anymore. For new project ,the new requirements are to have something else which can be used to read data from production database and then insert it to local database. 

I have been thinking about writing an API which will sit between desktop application and production database. Call to API method will be part of our normal sync process which will read data from one table in production db and then insert it to local table.WEB API will be published to our server which is on network and local database will be outside our network.

1- What kind of security i need to implement when reading data from API? Data will be in encrypted format. Is there any need of implementing token base authentication?

2- Is there any other better technique then API?

Answer 1

User1120430333 posted

2- Is there any other better technique then API?

SSSB if you are using MS SQL Server, it  can communicate with database engines database engine to database engine on the LAN or WAN, even MS SQL Express has SSSB. You can also use VB or C# .NET too since SSSB works with the CLR.

https://technet.microsoft.com/en-us/library/ms345108(v=sql.90).aspx#sqlsvcbr_topic18

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-service-broker?view=sql-server-2017

https://blogs.msdn.microsoft.com/sql_service_broker/2008/07/14/using-multiple-routes-in-service-broker/

Answer 2

User1724605321 posted

Hi sdyson31,

Token Based Authentication is more secure, more reliable and makes your system loosely coupled. It will be a better choice to create REST API using token-based authentication, if your API reached to broad range of devices like mobiles, tablets and traditional desktops.

Tokens are uniquely generated per application and site. If someone steals a token, they have not stolen your password, and that token is only good for that session only. 

In token-based authentication, you pass your credentials [user name and password], which go to authentication server. Server verifies your credentials and if it is a valid user then it will return a signed token(can access your database) to client system, which has expiration time. Client can store this token to locally using any mechanism like local storage, session storage etc and if client makes any other call to server for data then it does not need to pass its credentials every time. Client can directly pass token to server, which will be validated by server and if token is valid then you will able to access your data.

https://blogs.perficient.com/2017/06/11/token-based-authentication-in-web-api-2-via-owin/ 

Best Regards,

Nan Yu