1 2 Previous Next 22 Replies Latest reply on Oct 3, 2011 8:34 PM by beyerch2

    BD Import FDM script

    866553
      Hi all

      I'm running FDM v.11.1.2 and currently trying to access a MSQLServer table through a simple query.

      So far I've been able to connect to it but now I want to "secure" my script and not have to write my password as plain text.

      This is the line with the connection:

      Set cnSS= CreateObject("ADODB.Connection")

      ' Connect to sql
      cnSS.open "Provider= SQLOLEDB.1;Password=myPass;Persist Security Info= True;User ID=myUserId;Initial Catalog=myCatalog;Data Source=MyIP"


      Any ideas ? I've checked the manual but no further help is being given

      Thanks
        • 1. Re: BD Import FDM script
          beyerch2
          Your best bet would be to encrypt the connection string, bury it somewhere (i.e. registry), and then decrypt it before using in your scripting. The problem that you still have is that your encryption routine may also be accessible in which case, you are just adding a roadblock. (one that would keep the average Joe away). If you are really enterprising, you could wrap your encryption / decryption in a DLL and call that in the script so that anyone viewing the script would have zero idea as to how the string is encrypted or decrypted.

          The other option is you could establish a Trusted Connection between the FDM box and the SQL Server. In this situation, you don't need to pass a username/password in the connection string. If FDM is internal only INTRANET, there is nothing wrong with this. IF FDM is at all accessible via the Internet, this would be a bad idea. If the FDM box were ever compromised, then they would have permission to your database server as well. Not quite industry best practice.
          1 person found this helpful
          • 2. Re: BD Import FDM script
            Robb Salzmann
            Hi,

            To do this, you must first create an OLE DB Connection and use Windows NT Authentication / Integrated security. Start by creating a .udl file.

            Regards,
            Robb Salzmann
            • 3. Re: BD Import FDM script
              866553
              Thanks a lot for your quick reply, I'm not very skilled with DLL's and the like could you provide me with some pointers ?? maybe examples I could use to get this DLL thing going ??

              Also your other suggestion of the "encription algorithm" could work for me or at least I could try ... any examples ??

              Sorry I dont mean to be lazy is just I'm not very familiar with VB and even less with the FDM API

              Thanks again
              • 4. Re: BD Import FDM script
                SH_INT
                You have a number of options here are a couple to consider:

                1) Set up a UDL on the FDM application server and reference that in the connectionstring i.e. conn.ConnectionString = "FILE NAME=udl_location". If you do a google search for setting up UDL files there are plenty of examples.
                2) If you are using unified security you could put the store the user password credentials in the Global Logon User details in the Integration settings of the adapter, then replace the hard coded values for user and password in your connection string with variables that reference those values.

                Edited by: SH on Sep 28, 2011 6:12 PM
                • 5. Re: BD Import FDM script
                  Robb Salzmann
                  I'll reiterate. Use a UDL file. HFM and Planning have both used UDLs to connect to their repositories. This is probably the simplest way to get what you're trying to do.

                  http://www.sans.org/reading_room/whitepapers/application/securing-sql-connection-string_1371

                  Regards,
                  Robb Salzmann
                  • 6. Re: BD Import FDM script
                    beyerch2
                    Technically, UDL files are not encrypted and you are just moving the problem around so that it is not visible in the connection string. If someone has the authority to view the script with the connection string information, they most likely also have the information to view the UDL file. If you properly setup your permissions, it would be possible to put the UDL out of reach of someone looking at the script though.

                    On a side note, HFM has an "encrypted" UDL option; however, it is just a proprietary encryption algorithm used internally by HFM. (File extension would end in HFMUDL instead of UDL) Trying to access that file from your own VBSCript would be impossible unless you were to recreate the HFM decryption routine or find a DLL that you could call to decrypt it.

                    If you are bored, some 'fun' reading on security vulnerabilities / encryption on this stuff.


                    http://www.sans.org/reading_room/whitepapers/application/securing-sql-connection-string_1371

                    http://msdn.microsoft.com/en-us/magazine/cc164054.aspx --> mostly speaking in terms of .NET with the examples, but .....
                    • 7. Re: BD Import FDM script
                      Robb Salzmann
                      Agreed, however the OP is wanting to hide his Password and seems to want to keep things simple.


                      Regards,
                      Robb Salzmann
                      • 8. Re: BD Import FDM script
                        beyerch2
                        Using a UDL is NOT hiding a password, in my opinion.

                        A UDL is a plain text file that has all of the same information that you would need. It just isn't visible in the script. Anyone with half a brain would simply go to the UDL file, open it, and see the password. To "hide" the password, you would need to either : a.) Not use a password (i.e. Integrated Security) or b.) encrypt/obfuscate the password.

                        I guess we really should go back to the OP and ask him what his definition of "hide" is as everyone has their own definition. If he simply doesn't want it staring people in the eye when they open a script file, I would agree with you. That is the easiest route to go. Let's not kid anyone; however, into thinking that is a secure option. It is not.

                        I apologize, but this is a hot item for me because I spend an inordinate amount of time finding security vulnerabilities for people and I almost always hear back from people that "Oh, I thought that was secure -or- Oh, you can't see a password here, so I thought I was good". Also, it's raining today so that gets me cranky as well.
                        • 9. Re: BD Import FDM script
                          866553
                          Thank you all for your valuable comments.

                          Before starting this thread I was thinking about using some sort of hash function to encrypt the password and then using and algorithm to send the "decrypted" string to the connection.

                          Basically my problem is ... the code of the script is being audited so I just dont need the password to be anywhere in the script (if its in plain text) or anywhere on the file system (again unless it is not in plain text).

                          Now if the NT authentication fulfills the conditions I have to work with, then so be it.

                          Could you tell me if this UDL solution covers everything I've explained ???

                          Millions of thanks again

                          Edited by: HFMColUser on Sep 28, 2011 12:44 PM
                          • 10. Re: BD Import FDM script
                            TonyScalese
                            I'm with Charles on this one. A UDL is not secure. Also keep in mind that unless you are using SSL, the PW is getting passed anyway and anyone with 3/4 of a brain can figure out how to get it. But that's probably beyond the scope of this thread.

                            Cheer up Charles, it will be snowing there soon :)
                            • 11. Re: BD Import FDM script
                              Robb Salzmann
                              Charles,

                              I respect that this is a hot item for you. Clearly. I also appreciate your input - you know TONS more about this topic than I could ever forget.

                              However, may I suggest simply pointing out the flaws in my post isn't really moving the OP toward a solution.

                              PS. Pls keep the snow on your end of the hemisphere this year :)

                              HFMCol:
                              I think the the UDL using integrated security will get you where you want to be in terms of getting the password out of your code and from clear text transmission to using a secure mechanism to authenticate the connection. However, this is the limit of my knowledge. You will find beyerch can provide great detail on getting your VBScript ADO Connection as secure as possible beyond this UDL suggestion.

                              Regards,
                              Robb Salzmann
                              • 12. Re: BD Import FDM script
                                beyerch2
                                The only reason I pointed out the flaw in the UDL suggestion is that you and another person both provided it as the solution to this question. In order to ensure the OP doesn't go that route, unless that is all he really wanted to do, I pointed out the Cons of the option. Nothing personal to you or your solution, but it's just not really secure.

                                In regards to the snow, Long Beach was looking pretty nice ...........

                                To the OP :

                                If you want to take a look at Integrated Security/ Trusted Connection :

                                If SQL and FDM are on same box (highly doubt) : http://msdn.microsoft.com/en-us/library/aa984236(v=vs.71).aspx

                                If SQL and FDM are on different boxes : http://msdn.microsoft.com/en-us/library/aa984235(v=vs.71).aspx

                                The catch is that the out of the box stuff will cover the FDM application just fine. I am not 100% sure how it will work with the vbscript inside of the application. The ASP.NET impersonation may not carry over to the vbscript executing through FDM as it depends on how they implemented that. You could give it a whirl in a dev.

                                You may want to stick with the encryption option as that would be slightly easier to deal with?

                                Here is a sample for using RC4 in ASP / vbscript. The work is done in the .INC file at the bottom of the page :

                                http://www.4guysfromrolla.com/webtech/010100-1.shtml#postadlink

                                The problem with just rolling with this is that the encrypt and decrypt routines are still exposed as well as your call to the decrypt routine which would have to include your key. It's one step better than have a plain-text connection string, but once again, anyone with some programming ability could write their own script to call the decrypt and end up with the connection string. This MAY be enough to satisfy your audit; however, but I wouldn't recommend it.

                                The next step would be to make a COM object that has the encrypt, decrypt generic routines and then a hard coded decrypt call that just returns the connection string. The point of the hard coded call is that it will have the key and the encrypted string inside of it; therefore, the call from your vbscript will not have any parameters and there will be nothing to use by a would be hacker. The only way to determine the connection string would be to reverse engineer the COM object or get a copy of the COM object and call it through his own script.

                                I guess another way to do this would be to make a Webservice that can be called which will return the connection string. You could have the webservice only respond based on IP address of the requestor. This would prevent the attacker from getting any visibility to the source code and unless he is able to get on your local network and impersonate the IP address of your server(s), he could not get the output.

                                I'm slammed today; otherwise, I would provide you a sample. If my schedule opens up later, perhaps I'll put a sample web service out there to test.
                                • 13. Re: BD Import FDM script
                                  SH_INT
                                  If someone has the authority to view the script with the connection string information, they most likely also have the information to view the UDL file...

                                  Can't really agree with this one. There is no need for someone who has FDM admin rights and can therefore view scripts via the web interface or workbench client to have access to the application server where the UDL is stored, especially in a Production environment. Going down an encryption route seems rather overkill, however, that's just my opinion. Anyway my option 2 would work fine on the plain text issue.
                                  • 14. Re: BD Import FDM script
                                    beyerch2
                                    SH,

                                    Yes I would agree that I'm making a pretty big assumption there and 'most' is probably going overboard. Without knowing the exactly situation, hard to make a generalization. If you are trying to lock out remote users doing Web editing of scripts, yes you can most likely lock down the UDL file so that they cannot physically get to it through permissions. (I did point this out in my first post)

                                    It sounds like in this case; however, the OP has an audit requirement that no plain text passwords are stored. (I have similar SOX requirements here as well) In that scenario, a UDL would not be optimal because anyone, including non-FDM users, with system access could view the UDL, etc. Of course, most of those people are probably system admins who don't need to see the password because they either know it or could reset it, but lets not question internal audit/SOX policy. :-)
                                    1 2 Previous Next