ODBC connection on Windows Server 2008 and Word Merge Issues

This might be a complicated one to explain...

I have a custom application written in vb.net that has a button that when clicked launches word, and merges some fields from a SQL database using an ODBC connection. I have attached a sample of the merge code for reference but that works fine.  

The problem is the ODBC connection.  We store the ODBC connection and the credentials in the app.config file and there is a copy of the SystemDSN / ODBC connector on each machine which gets installed through group policy.  

Recently we have had remote users who connect via terminal server to use this application.  They all connect to a machine running windows server 2008 R2 Enterprise.  When they attempt to run the merge they get a dialog box asking for the SQL password (even though the password is in the System DSN).  Even if the correct password is put in the merge fails.  This only occurs on the Windows Server 2008 machine.  This can be resolved by logging in as each user and creating a user DSN identical to the System DSN that is currently in place.  This is a growing problem as we have more and more remote users needing the system.  

The only current solutions are:  

1. Continue to create individual user DSN's (time consuming)


2. Have each user log into a separate Windows 7 machine because Windows 7 doesn't have this problem but Windows 7 can have only one remote user at a time.  This is not feasible because of the cost of individual machines and the growing number or remote users.

We have looked into creating the user DSN via group policy but that apparently is not an available feature in Windows Server.  What I am asking is does anyone know why Windows 2008 Server requires a user DSN to let MS Word connect to SQL via ODBC?  

I am guessing it is a security setting of some sort but I don't know where to begin to look.  Ideally I need the server to behave like Windows 7 where only the system DSN is necessary to merge.

Other relevant facts:  

Operating System:       Windows Server R2 2008
SQL Version :             SQL Server 2008 R2
MS Word Version :       2010

All latest updates and patches applied on a regular basis.

I realize I will probably need to expand as the experts ask follow up questions but I will monitor this question closely and try to respond quickly during normal business hours PST.

Dim oApp As Object = Nothing
        Dim oDoc As Object = Nothing
            oApp = CreateObject("Word.Application")
            oDoc = CreateObject("Word.Document")
        Catch ex As Exception
        End Try

        Dim FullDocPath As String = Me.strDocPath & Me.strDocumentName

        If var_DoNotKillWord = True Then

                If Not fKillProcess("winword") Then
                    'kryptonmessagebox.Show("Could not close winword")
                End If
                If Not fKillProcess("winword") Then
                    'kryptonmessagebox.Show("Could not close winword")
                End If
            Catch ex As Exception

            End Try
        End If

        Dim intOffset As Single = 0
            'Start a new document in Word
            oApp = CType(CreateObject("Word.Application"), Word.Application)

                oDoc = oApp.Documents.Open(CType(FullDocPath, System.Object), False, True, False, , , True, , , Word.WdOpenFormat.wdOpenFormatDocument, , True)
            Catch MyException As System.Exception

                CType(oApp, Word.Application).Quit()
                oApp = Nothing
                Return False
                Exit Function
            End Try

            oApp.Visible = True

            ' Do the Merge
            oApp.DisplayAlerts = False
            Dim TestStrings As New StringBuilder
            TestStrings.Append("With oDoc.MailMerge" & vbCrLf)
            'Added by Tom Harris Jan 2010 to add default styles and font size

            With oDoc.Styles(wdStyleNormal).Font
                .Name = "Arial"
                .Size = 10
                .Bold = False
                .Italic = False
                .Underline = wdUnderlineNone
                .UnderlineColor = wdColorAutomatic
                .StrikeThrough = False
                .DoubleStrikeThrough = False
                .Outline = False
                .Emboss = False
                .Shadow = False
                .Hidden = False
                .SmallCaps = False
                .AllCaps = False
                .Color = wdColorAutomatic
                .Engrave = False
                .Superscript = False
                .Subscript = False
                .Spacing = 0
                .Scaling = 100
                .Position = 0
                .Kerning = 0
                .Animation = wdAnimationNone
            End With

            With oDoc.MailMerge

                'View the Sql Statement for testing purposes
                Dim FullSql As String = var_SqlStatementPart1 & " " & var_SqlStatementPart2
                TestStrings.Append(FullSql & vbCrLf)
                TestStrings.Append(var_DataConnectionFilePath & vbCrLf)
                TestStrings.Append(var_ConnectionString & vbCrLf)
                TestStrings.Append(SqlStatementPart1 & vbCrLf)
                TestStrings.Append(FullDocPath & vbCrLf)

                Dim SQLString2 As String = ""

                    .OpenDataSource(Name:= _
                   var_DataConnectionFilePath _
                   , ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=False, _
                   AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
                   WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
                   Format:=wdOpenFormatAuto, Connection:= _
                   var_ConnectionString _
                    , SQLStatement:=SqlStatementPart1, SQLStatement1:=SqlStatementPart2, SubType:= _
                    .Destination = Word.WdMailMergeDestination.wdSendToNewDocument
                    .SuppressBlankLines = True
                Catch ex As Exception
                End Try

                With .DataSource
                    .FirstRecord = Word.WdMailMergeDefaultRecord.wdDefaultFirstRecord
                    .LastRecord = Word.WdMailMergeDefaultRecord.wdDefaultLastRecord
                End With
                Catch ex As Exception

                End Try

            End With

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ayman BakrSenior ConsultantCommented:
Windows 2008 server R2 is a 64 bit OS.

A system DSN can either be a 32 bit or a 64 bit process. I would guess that your created DSN  was intended for a 32 bit OS and thus is saving the files in:

C:\Program Files\Common Files\ODBC\Data Sources

But on Windows 2008 R2 the above path is used for 64 bit. Windows 2008 R2 recognizes the correct path for a 32 bit under 'Program Files (X86)'.

To resolve your issue copy the DSN files to the following path:

C:\Program Files (x86)\Common Files\ODBC\Data Sources

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pensiongeniusAuthor Commented:
Sorry for being so slow to respond, the ever changing project priority took me off this issue for a while.  I looked into your solution Mutawadi but the file location didn't exist.  after \Common Files\ the ODBC folder wasn't there (visible or hidden).  Currently we have added the user DSN to work around the problem for now.  

As my company is putting this on the back burner I am also putting this on the back burner but your comment moved me closer to the solution although I don't know if this is it, we have a Windows 7 64-bit installation and that doesn't share the problem.  

I am leaning toward it being a security issue on the server OS and of course I can't post a detailed list of the security settings (partially because I don't have access to them all, I am IS staff , not IT staff).
pensiongeniusAuthor Commented:
Thanks for the help, it is getting us in the right direction but we aren't coming back to this issue for a while so I am awarding points for now and will re-visit it when company priorities allow me to.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2008

From novice to tech pro — start learning today.