hindersaliva
asked on
Connect to Azure SQL database with Excel and ADO
I have a new SQL database on Azure. I can connect to it from SQL Server Management Studio. And I can connect to it from Excel via the Ribbon.
But I can't connect with the usual way I do with ADO.
I tried this connection string, as given by Azure portal. (you can try with my live credentials. No sensitive data at the moment)
I have also tried this, from the usual connection string I use to connect to my other SQL Server hosting online:
I tried recording a macro to see what I should use but Excel skips recording the connection details.
More info: I have set the firewall permissions on Azure to 0.0.0.0 to 255.255.255.255.
Thanks for any help.
(Also: if for any reason Azure is not suitable for my purpose of allowing many distributed Excel spreadsheets connect to an online SQL Server if there an alternative? Amazon Web Services?)
But I can't connect with the usual way I do with ADO.
I tried this connection string, as given by Azure portal. (you can try with my live credentials. No sensitive data at the moment)
"Server=tcp:reimagineexcel.database.windows.net,1433;Initial Catalog=ReimagineExcel;Persist Security Info=False;User ID=hiran@reimagineexcel;Password=XXXXXXXXXXXX;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
I have also tried this, from the usual connection string I use to connect to my other SQL Server hosting online:
"Provider=sqloledb; Data Source=tcp:reimagineexcel.database.windows.net,1433;Network Library=DBMSSOCN; Initial Catalog=ReimagineExcel;User ID=hiran@reimagineexcel;Password=XXXXXXXXXX;"
I tried recording a macro to see what I should use but Excel skips recording the connection details.
More info: I have set the firewall permissions on Azure to 0.0.0.0 to 255.255.255.255.
Thanks for any help.
(Also: if for any reason Azure is not suitable for my purpose of allowing many distributed Excel spreadsheets connect to an online SQL Server if there an alternative? Amazon Web Services?)
ASKER
I've changed the password now.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I removed the 'tcp:' prefix from the server. I used this connection string.
Open in new window
To connect with SQL Server Management Studio (maybe) you need the 'tcp:' prefix, but not when connecting with ADO?
(I'll keep the password unchanged for 24 hours in case anybody wants to try it. Will change in tomorrow evening GMT)