May 29, 2017 at 5:27 am
Hello guys/girls,
what will be the best way (and secure) to create a connection from Excel VBA (ADODB) to Azure SQL database?
The users will use this workbook from their desktop machines so there a static IP is not a solution.
Is this secure and what else do I need to setup?
Sub GetDataFromADO()
'Declare variables'
Set objMyConn = New ADODB.Connection
Set objMyRecordset = New ADODB.Recordset
Dim strSQL As String
'Open Connection'
objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=myAzureServer;Initial Catalog=MyDatabase;User ID=abc;Password=abc;"
objMyConn.Open
'Set and Excecute SQL Command'
strSQL = "select * from myTable"
'Open Recordset'
Set objMyRecordset.ActiveConnection = objMyConn
objMyRecordset.Open strSQL
'Copy Data to Excel'
ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)
End SubJune 1, 2017 at 8:01 am
Connecting from ADO in Powershell should be the same as VBA. I use the full server name provided: xxx.database.windows.net. That ought to work for you. You will need to make sure you allow for the fact that the firewall will block individual IP addresses, so if you have a range of those at work, add it to the firewall now.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply