1

MS Word documents have a Visual Basic component that allows processes to be executed when specified events occur.

One of these events is Document_New(), that can be used like this to pop up a "UserForm" that is also part of the Word document:

Private Sub Document_New()
    Dim myForm As UserForm1
    Set myForm = New UserForm1
    myForm.Show
End Sub

The UserForm looks just like a VB6 or MS Access form and various controls can be added to collect input from the user. In this case a "Submit" button would be place that has an OnClick() event that accesses the database and returns a recordset used in the document/template.

The code behind can run SQL queries on a SQL server usinging either a DAO or ADODB object model.

The goal is to run some SQL query or stored procedure that returns the data needed for the Word Document. This can all be done with code and there are a million examples and ways to do this.

However... The connection string for the ODBC driver or the ADODB connection leaves the servername/username/password visible to whomever has access to the Word document or template (i.e. user opens document/template in Word and looks at the VB code and now can connect and do anything to the database tables).

How can I create this functionality but not allow the connection string properties to be visible to someone using the document/template?

If it was a VB6 application, it could be compiled. But this is just a Word document or template and not compileable.

I don't believe that the "Encrypted" functionality applies here. That just encrypts data across the network. Connection string properties are still visible to the prying eye.

2

Configure the database to use Windows authentication, then add IntegratedSecurity=true to the connection string. This will have the connection use the logged-in user's authority to connect to the database.

This protects you in several ways. As you require, no user/password is kept in the connection string.

Another way this protects you is that it's the user's authority that connects to the database, not some Word document. That is important because the authority shouldn't travel around hidden inside a document. The user controls their own authority.

Finally, if you're using Active Directory, you can use AD group membership to fine-tune the access to the database, ensuring only approved group members can use the Word document to access the database at whatever levels you desire.

  • Thanks John Deters. This sound like a great solution however I don't think users have any Windows Authentication for the databases. Currently they do everything through compiled VB6 or SSRS or Crystal reports. But this definitely is an answer worth accepting should I not get any other solutions. I will also check with my higher ups at work to see what implementing this idea entails. I am currently lowest on the Totem Pole (hence why I have to build the reports . . . yeck). – JustJohn Oct 3 '17 at 5:06
1

How can I create this functionality but not allow the connection string properties to be visible to someone using the document/template?

You can't. The connection string will be visible to anyone who has the file. This is true, by the way, even if it is a compiled application. It just takes a little bit more work to get them out.

So what to do?

Joahn Deters suggestion is a good one. However, even if you do that users will still have full read- and write access to your database. That is not optimal.

No matter if you follow Johns advice or not, you should also follow the principle of least privelige. That means that the database user (be it from the credentials in the connection string, or the Windows integrated security) should only be allowed to access the information she needs. So limit her priveliged to only read from the necesarry tables, and write nowhere.

  • Yes, good advice. The idea of making a group for users to use Windows Authentication that has read-only privileges is a possible long term option. After all they have access to this data when logging into the main vb6 application that administers all the data the report would use. I like your mention of the principle of least privilege. – JustJohn Oct 3 '17 at 22:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.