Put SQL Authentication inside Excel Workbook

Question

I have an Excel workbook with a Microsoft SQL Server connection. I want to be able to send this workbook with the connection so that others can use it, but they do not have SQL Server licenses.

Is there any way to include my authentication within the workbook so that the connection will still work?

Thanks!

Answer 1

You should create only one connection to the database, but allow multiple Excel sheets to access it simultaneously.

To do that, create a program or a service that can access the database, while it serves requests from Excel (or other clients).

You should look into technology like WCF or REST (ASP.NET, Owin, ...) if you want to use Windows technology. You could also create services using other platforms like for example Java.

Answer 2

Just create a connection string to the SQL Server and include it in your Excel sheet, as long as the user is connected to the local domain they should be able to use the excel sheet. Something like: in your setting sheet

ConnectionString=
Provider=SQLOLEDB;Data Source=YourSQLServerName;Initial Catalog=YourDatabase;Integrated Security=SSPI;Trusted_Connection=Yes;

and in VBA

Set wsSettings = ThisWorkbook.Sheets("Settings")
Application.DisplayStatusBar = True
    Application.StatusBar = "Contacting SQL Server..."

    Dim conn As ADODB.Connection, rs As ADODB.Recordset
    Set conn = New ADODB.Connection
    conn.Open wsSettings.Range("ConnectionString").value

You don't need the licence to connect to a SQL Server on the same domain.E.G over a billion people use Facebook and Facebook DB stores user profile but the users don't have to pay for using Facebook DB, users are connecting to DB and Facebook pays for the licence to use the DB. Your company has paid the licence so any number of people can connect and retrieve data but the can't install DB on their machine without a licence.