Link to home
Start Free TrialLog in
Avatar of hindersaliva
hindersalivaFlag for United Kingdom of Great Britain and Northern Ireland

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)
"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;"

Open in new window


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;"

Open in new window


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?)
Avatar of hindersaliva
hindersaliva
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Bingo! I got it to work.
I removed the 'tcp:' prefix from the server. I used this connection string.
"Provider=sqloledb; Data Source=reimagineexcel.database.windows.net,1433;Network Library=DBMSSOCN; Initial Catalog=ReimagineExcel;User ID=hiran@reimagineexcel;Password=XXXXXXXXXX;"

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)
I've changed the password now.
ASKER CERTIFIED SOLUTION
Avatar of Shaun Vermaak
Shaun Vermaak
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial