We provide a large software suite, which amongst its functionality includes the ability to perform Mail Merges in MS Word / Office 365.
In order for Word to know where to get the merge field list and data from, we set up a .UDL file:-
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;
Data Source=SQLInstanceInPlainText\SQLServerInPlainText;
Initial Catalog=DatabaseCatalogInPlainText;
User ID=UsernameInPlainText;
Password=PasswordInPLainText;
Persist Security Info=True;
As indicated, all the information in a .UDL file is stored in plain text, including Username and Password (because Microsoft and security are two mutually exclusive concepts).
Up to now, this hasn't been a major issue: all of our clients have our software and their databases installed on a server physically located on their premises, and not externally accessible.
So, there was a security hole, but given someone would have to be physically in the building to take advantage of it, it was trivial.
However, we now have several companies (and more to come, no doubt) wanting to move over to a cloud-based system. We have duly modified our software to work entirely over Azure too, and are in the process of testing it.
And during the testing, we've come to the point where we need to update the .UDL file to point to the SQL Azure database.
This database is of course not physically located in their premises, it's in the cloud, and as such can be accessed from anywhere. In the case of at least one company, they want it accessible from people working at home / on site etc, and currently don't have any form of VPN set up to allow us to lock down which IP addresses can access it (we are trying to talk them into that!).
So, if we use a .UDL file, we are potentially putting this (and undoubtedly others) company in the situation someone can leak the database connection details and that's it, game over.
So, I'm looking for an alternative to a .UDL file, somehow, that will allow us to still use MS Word / Office 365 to perform mail merges.
Something that has encrypted details would at least be an improvement, but an ideal solution for us would be for MS Word / Office 365 to request the details from an .EXE program.
None of the rest of our software has hard-coded connection strings (e.g. in app.config), they're all stored heavily encrypted on our server. When the software starts, it requests the encrypted connection details for the selected database (most companies have several, e.g. a Live, a Test and a UAT, and their version of the software can only see their databases) from our server.
If it was possible for MS Word / Office 365 (perhaps via an Add-In, I've written Outlook Add-Ins so could give it a go) to request the connection details from a program instead of a .UDL file, we could apply the same solution and require the User to select the database and log in (with their software Username and Password, NOT the SQL Username and Password).
So, not only does no-one know the SQL connection details, any User that leaves has their software Username and Password revoked, so even if they still have the software (for example on a laptop) they can no longer access the system.
Likewise, if that laptop is lost or stolen, the person that ends up with it still can't get the details, because they won't have a login to our software.
Assuming, of course, that it's somehow possible to get MS Word / Office 365 to get the connection details from anything other than a file containing them in plain text...
TL;DR...
MS Word / Office 365 uses a .UDL file for SQL connection details for Mail Merges.
These details are stored in plain text, and so are a nasty security hole.
Locking down the database to specific IPs is not currently an option (clients, not us!)
We're looking for an alternative to .UDL files, ideally MS Word / Office 365 requesting the details from a .EXE program, but at a minimum something that stores the details in an encrypted form.
EDIT:
I mentioned a Word Add-In, if Word can use one of those to request the connection details directly (not through a separate .EXE program) then that would also be a great solution. The Outlook Add-In I wrote already does this, so I could probably pinch most of the code if someone could point me in the correct direction.
Edit: Attempt at Encrypting Connection String for ODC files
I found the following code, which encrypts connection strings for web.config and app.config
// Protect the connectionStrings section.
private static void ProtectConfiguration()
{
System.Configuration.Configuration config = ConfigurationManager.
OpenExeConfiguration(ConfigurationUserLevel.None);
// Define the Rsa provider name.
string provider = "RsaProtectedConfigurationProvider";
// Get the section to protect.
ConfigurationSection connStrings = config.ConnectionStrings;
if (connStrings != null)
{
if (!connStrings.SectionInformation.IsProtected)
{
if (!connStrings.ElementInformation.IsLocked)
{
// Protect the section.
connStrings.SectionInformation.ProtectSection(provider);
connStrings.SectionInformation.ForceSave = true;
config.Save(ConfigurationSaveMode.Modified);
Console.WriteLine("Section {0} is now protected by {1}",
connStrings.SectionInformation.Name,
connStrings.SectionInformation.ProtectionProvider.Name);
}
else
Console.WriteLine(
"Can't protect, section {0} is locked",
connStrings.SectionInformation.Name);
}
else
Console.WriteLine(
"Section {0} is already protected by {1}",
connStrings.SectionInformation.Name,
connStrings.SectionInformation.ProtectionProvider.Name);
}
else
Console.WriteLine("Can't get the section {0}",
connStrings.SectionInformation.Name);
}
So, I wrote a quick program, and slapped the connection string to encrypt into its app.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
</startup>
<connectionStrings>
<add name="ODC" connectionString="Provider=SQLOLEDB.1;Password=Password;Persist Security Info=True;User ID=Username;Data Source=Server\Instance;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Database" providerName="System.Data.EntityClient" />
</connectionStrings>
</configuration>
Running the program, the .exe.config now contained the following
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
</startup>
<connectionStrings configProtectionProvider="RsaProtectedConfigurationProvider">
<EncryptedData Type="http://www.w3.org/2001/04/xmlenc#Element"
xmlns="http://www.w3.org/2001/04/xmlenc#">
<EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#tripledes-cbc" />
<KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
<EncryptedKey xmlns="http://www.w3.org/2001/04/xmlenc#">
<EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-1_5" />
<KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
<KeyName>Rsa Key</KeyName>
</KeyInfo>
<CipherData>
<CipherValue>r40SAb8XRp6w8KLAi+QOZiU9wBDBdQ5Z57QqibCdBTX1KlMXTGorCtjZS1jEzsRt+2qqTb1pRqkC81a8NSbEY0CtuR03nq8Wn8nFp+pEpNnT0fWEvxw9oCAF7HhxcrRao24AbMNzO+RnBIxDtBiCRieQdaQvR6Bp+//LheE8i6Z7MAeTPbKvD2RyFXBxEJ45MopNgGpq511GDaLen9tcaGPwRjO20Hwhoc2po1viqLd/UzEhpFFDrb7ffZm+p5ghUOjcysNHSnbUUJcNnv6z+IemTMytG6Ikr11cACs0NMfXeuA3Ab20btBoBILNq6I+l82p3gXKkNeCz+JV8UmCJA==</CipherValue>
</CipherData>
</EncryptedKey>
</KeyInfo>
<CipherData>
<CipherValue>Bj+N17Mh2Wsdj/gfutYomGo7NctoEHgJyE3NXqfX2+s6jtBTOyNJJihIg5e8elRdf9kJlRP0mrJievFP6LrUZsGoIGE6Z6Ldz7sPE4f1kgdcQEUGBTA2Ir0qnfR05Vk0QL/3MTnTg12BB7U5V742NOQfdrdYqqxC4faFSYlW3ETnlrKWVXLVEijI0ovjq+f3rGBsgbOVCOO+lnkiMDBOJnqklfte9KbkgQ44Kju6buveltINGDNZl2YD7g4RxyyMhkbSfwDvPvO2rr4kZgy843kVwl0sv2LwBErprBtt6gaCxTriH4V1rq02lUVyXDVr4oeynJxQbeRy4Uha2j5U9kk8KuWhi5XL+6aNazvKhWxp+EiliciBVPHffT/1uu3IKkLRD+k4mZMV+bL+1rkufCXno07g3KpfXkA6WhbxI0XayIMj/QY00VkPSSq8dfRu+5tWAz1D3VgHMgC9yHQGwV6TpC/ONnFFLRxKhKIv9nEiUi0MTDdMZfINotnoCYmgu8ylfkvBRSYIITat4ZHiU48B29MXLYYA6KRHMKi/v+o=</CipherValue>
</CipherData>
</EncryptedData>
</connectionStrings>
</configuration>
So, I grabbed that and tried it in the ODC file I had, but Word just said 'Record 1 contained too few data fields', and when I clicked Ok it said 'Record 2 contained too few data fields', and then repeated a couple of times
Below is the ODC file in a working state, with the new section commented out.
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
<meta name=ProgId content=ODC.Table>
<meta name=SourceType content=OLEDB>
<meta name=Catalog content="OurServer">
<meta name=Schema content=dbo>
<meta name=Table content="uvw_OurView">
<title>uvw_MMClientDetails</title>
<xml id=docprops><o:DocumentProperties
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns="http://www.w3.org/TR/REC-html40">
<o:Description>Test</o:Description>
<o:Name>uvw_OurView</o:Name>
</o:DocumentProperties>
</xml><xml id=msodc><odc:OfficeDataConnection
xmlns:odc="urn:schemas-microsoft-com:office:odc"
xmlns="http://www.w3.org/TR/REC-html40">
<odc:Connection odc:Type="OLEDB">
<odc:ConnectionString>Provider=SQLOLEDB.1;Password=Password;Persist Security Info=True;User ID=Username;Data Source=Server\Instance;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Database</odc:ConnectionString>
<!--
<connectionStrings configProtectionProvider="RsaProtectedConfigurationProvider">
<EncryptedData Type="http://www.w3.org/2001/04/xmlenc#Element"
xmlns="http://www.w3.org/2001/04/xmlenc#">
<EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#tripledes-cbc" />
<KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
<EncryptedKey xmlns="http://www.w3.org/2001/04/xmlenc#">
<EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-1_5" />
<KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
<KeyName>Rsa Key</KeyName>
</KeyInfo>
<CipherData>
<CipherValue>r40SAb8XRp6w8KLAi+QOZiU9wBDBdQ5Z57QqibCdBTX1KlMXTGorCtjZS1jEzsRt+2qqTb1pRqkC81a8NSbEY0CtuR03nq8Wn8nFp+pEpNnT0fWEvxw9oCAF7HhxcrRao24AbMNzO+RnBIxDtBiCRieQdaQvR6Bp+//LheE8i6Z7MAeTPbKvD2RyFXBxEJ45MopNgGpq511GDaLen9tcaGPwRjO20Hwhoc2po1viqLd/UzEhpFFDrb7ffZm+p5ghUOjcysNHSnbUUJcNnv6z+IemTMytG6Ikr11cACs0NMfXeuA3Ab20btBoBILNq6I+l82p3gXKkNeCz+JV8UmCJA==</CipherValue>
</CipherData>
</EncryptedKey>
</KeyInfo>
<CipherData>
<CipherValue>Bj+N17Mh2Wsdj/gfutYomGo7NctoEHgJyE3NXqfX2+s6jtBTOyNJJihIg5e8elRdf9kJlRP0mrJievFP6LrUZsGoIGE6Z6Ldz7sPE4f1kgdcQEUGBTA2Ir0qnfR05Vk0QL/3MTnTg12BB7U5V742NOQfdrdYqqxC4faFSYlW3ETnlrKWVXLVEijI0ovjq+f3rGBsgbOVCOO+lnkiMDBOJnqklfte9KbkgQ44Kju6buveltINGDNZl2YD7g4RxyyMhkbSfwDvPvO2rr4kZgy843kVwl0sv2LwBErprBtt6gaCxTriH4V1rq02lUVyXDVr4oeynJxQbeRy4Uha2j5U9kk8KuWhi5XL+6aNazvKhWxp+EiliciBVPHffT/1uu3IKkLRD+k4mZMV+bL+1rkufCXno07g3KpfXkA6WhbxI0XayIMj/QY00VkPSSq8dfRu+5tWAz1D3VgHMgC9yHQGwV6TpC/ONnFFLRxKhKIv9nEiUi0MTDdMZfINotnoCYmgu8ylfkvBRSYIITat4ZHiU48B29MXLYYA6KRHMKi/v+o=</CipherValue>
</CipherData>
</EncryptedData>
</connectionStrings>
-->
<odc:CommandType>Table</odc:CommandType>
<odc:CommandText>"Database"."dbo"."uvw_OurView"</odc:CommandText>
</odc:Connection>
</odc:OfficeDataConnection>
</xml>
<style>
<!--
.ODCDataSource
{
behavior: url(dataconn.htc);
}
-->
</style>
</head>
<body onload='init()' scroll=no leftmargin=0 topmargin=0 rightmargin=0 style='border: 0px'>
<table style='border: solid 1px threedface; height: 100%; width: 100%' cellpadding=0 cellspacing=0 width='100%'>
<tr>
<td id=tdName style='font-family:arial; font-size:medium; padding: 3px; background-color: threedface'>
</td>
<td id=tdTableDropdown style='padding: 3px; background-color: threedface; vertical-align: top; padding-bottom: 3px'>
</td>
</tr>
<tr>
<td id=tdDesc colspan='2' style='border-bottom: 1px threedshadow solid; font-family: Arial; font-size: 1pt; padding: 2px; background-color: threedface'>
</td>
</tr>
<tr>
<td colspan='2' style='height: 100%; padding-bottom: 4px; border-top: 1px threedhighlight solid;'>
<div id='pt' style='height: 100%' class='ODCDataSource'></div>
</td>
</tr>
</table>
<script language='javascript'>
function init() {
var sName, sDescription;
var i, j;
try {
sName = unescape(location.href)
i = sName.lastIndexOf(".")
if (i>=0) { sName = sName.substring(1, i); }
i = sName.lastIndexOf("/")
if (i>=0) { sName = sName.substring(i+1, sName.length); }
document.title = sName;
document.getElementById("tdName").innerText = sName;
sDescription = document.getElementById("docprops").innerHTML;
i = sDescription.indexOf("escription>")
if (i>=0) { j = sDescription.indexOf("escription>", i + 11); }
if (i>=0 && j >= 0) {
j = sDescription.lastIndexOf("</", j);
if (j>=0) {
sDescription = sDescription.substring(i+11, j);
if (sDescription != "") {
document.getElementById("tdDesc").style.fontSize="x-small";
document.getElementById("tdDesc").innerHTML = sDescription;
}
}
}
}
catch(e) {
}
}
</script>
</body>
</html>
I tried it with the connection string as it stands, called connectionStrings, and as the original was, called odc:ConnectionString, but neither worked.
Anyone know if I'm on the right track here, and just need to tweak something, or can ODC files / Word / Office 365 not handle encryption and configProtectionProvider="RsaProtectedConfigurationProvider" ?
I solved this in two parts
1) The Mail Merge
The actual Mail Merge itself is performed by the main C# program using the Spire.PDF library. This removes the need for a UDL or ODC file for getting the data from the database into the documents.
2) The Template File
This still left the issue of creating the Mail Merge template file, with the merge fields from the views. This had to be doable from outside the main C# program, and previously required a UDL file.
I solve this issue by writing a Word Add-In, based on code from a blog on an Add-In company's site: https://www.add-in-express.com/creating-addins-blog/2013/07/05/automating-word-mail-merge/
This Add-In adds a section to the 'Add-Ins' ribbon, named '[Our Product] Mail Merge', with a button 'Add Merge Fields'. Clicking the button opens a new form.
Note: This is an early version to test everything works, so not particularly pretty, missing some error checking etc.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using Word = Microsoft.Office.Interop.Word;
using System.Runtime.InteropServices;
namespace RPMWordMailMergeAddIn
{
public partial class MailMergeForm : Form
{
private static string connectionString = null;
public MailMergeForm()
{
InitializeComponent();
if (connectionString != null)
{
btnConnect_Click(null, null);
}
}
private void btnConnect_Click(object sender, EventArgs e)
{
if (connectionString == null || sender != null)
{
connectionString = <Call our 'Login to the Software' screen,
and return the restricted database user connection details>
tvMailMergeTables.Nodes.Clear();
}
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
var views = connection.GetSchema("Views");
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.ConnectionString = connectionString;
TreeNode rootNode = new TreeNode(builder.InitialCatalog, 1, 1);
rootNode.Tag = "Server";
TreeNode viewsNode = rootNode.Nodes.Add("Views", "Views", 2, 2);
foreach (DataRow view in views.Rows)
{
var viewName = view.ItemArray[2].ToString();
if (viewName.StartsWith("uvw_MM") && !viewName.EndsWith("_X"))
{
TreeNode tableNode = viewsNode.Nodes.Add(viewName, viewName, 3, 3);
tableNode.Tag = "View";
var schemaOptions = new string[4];
schemaOptions[2] = viewName;
var columns = connection.GetSchema("Columns", schemaOptions);
foreach (DataRow column in columns.Rows)
{
var columnName = column.ItemArray[3].ToString();
TreeNode columnNode = tableNode.Nodes.Add(columnName, columnName, 6, 6);
columnNode.Tag = "Column";
}
}
}
tvMailMergeTables.Nodes.Add(rootNode);
rootNode.Expand();
viewsNode.Expand();
connection.Close();
}
}
private void tvMailMergeTables_NodeMouseDoubleClick(object sender, TreeNodeMouseClickEventArgs e)
{
TreeNode selectedNode = e.Node;
if (selectedNode.Tag.ToString() == "Column" && selectedNode.IsSelected) // Fix for TreeView expanding bug
{
Word.Application wordApp = null;
Word.Document doc = null;
Word.Selection selection = null;
Word.MailMerge wordMerge = null;
Word.MailMergeFields wordMergeFields = null;
try
{
wordApp = ThisAddIn.application;
doc = wordApp.ActiveDocument;
wordMerge = doc.MailMerge;
wordMergeFields = wordMerge.Fields;
selection = wordApp.Selection;
wordMergeFields.Add(selection.Range, selectedNode.Text);
}
finally
{
if (wordMergeFields != null)
{
Marshal.ReleaseComObject(wordMergeFields);
}
if (wordMerge != null)
{
Marshal.ReleaseComObject(wordMerge);
}
if (selection != null)
{
Marshal.ReleaseComObject(selection);
}
if (doc != null)
{
Marshal.ReleaseComObject(doc);
}
}
}
}
}
}
This form then asks the User to log in to our software, and if they successfully do so it returns the connection string for a read-only database User, who only has access to the Mail Merge views.
A TreeView control is then populated with the views and their columns.
Double-clicking on a column name will add it to the Word document as a merge field at the current cursor position.
When saved, the Word document does not store any of the connection details.
So:-
Just what I was after!
Additional
No longer required as part of this solution, but for completeness sake, it's also possible to do the Mail Merge from within the Add-In.
This requires an ODC file, but you can leave the Connection String section empty and pass it the Connection String instead, thus avoiding having details stored in plain text.
private void btnPerformMailMerge_Click(object sender, EventArgs e)
{
Word.Application wordApp = null;
Word.Document doc = null;
Word.MailMerge wordMerge = null;
Word.MailMergeFields wordMergeFields = null;
object sqlQuery = string.Empty;
object connection = string.Empty;
string odcPath = string.Empty;
Object oMissing = System.Reflection.Missing.Value;
Object oFalse = false;
try
{
wordApp = ThisAddIn.application;
doc = wordApp.ActiveDocument;
wordMerge = doc.MailMerge;
wordMergeFields = wordMerge.Fields;
connection = connectionString;
sqlQuery = String.Format("Select * From \"{0}\"", viewName);
var name = @"Empty.odc";
wordMerge.OpenDataSource(name, ref oMissing,
ref oMissing, ref oMissing, ref oMissing,
ref oMissing, ref oMissing, ref oMissing,
ref oMissing, ref oMissing, ref oMissing,
ref connection, ref sqlQuery, ref oMissing,
ref oMissing, ref oMissing);
wordMerge.Destination = Word.WdMailMergeDestination.wdSendToNewDocument;
wordMerge.Execute(ref oFalse);
}
finally
{
if (wordMergeFields != null)
{
Marshal.ReleaseComObject(wordMergeFields);
}
if (wordMerge != null)
{
Marshal.ReleaseComObject(wordMerge);
}
if (doc != null)
{
Marshal.ReleaseComObject(doc);
}
}
}