CODEWITHSUNDEEP
sql-serverazureazure-sql-database
Ravi Shet
Ravi Shet

Reputation: 23

Allowing a specific user of Azure SQL Server Database to connect only through a specific IP

In Azure Server/Database firewall we can set the IP from which connections to the SQL Server can be made. However if I set any IP address eg. 101.202.203.204, then all users of the database can connect through that IP.

There is one user which is used by the application. I do not want anyone to connect to the production database using this username. This connection should only be permissible from the VM hosting the application in Azure.

In SQL Server there is a feature named Logon trigger wherein I can determine the username and the IP address from where the connection is being made and accordingly refuse if the connection request is invalid. This feature would have been helpful, however this feature is not available in the Azure SQL Server which I am using as a service.

Thanks in advance.

Upvotes: 1

Views: 584

Answers (1)

Conor Cunningham MSFT
Conor Cunningham MSFT

Reputation: 4491

I'm not sure that this is the best way to secure a SQL Server/SQL Azure database, but technically you can do this using a login trigger where you check the user name and the IP address in the trigger. There's an example in the docs for triggers here that is basically what you are requesting (checks the login name). You'd just need to join with sys.dm_exec_connections and validate that the IP address is what you wanted.

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017

Upvotes: 1

Related Questions