Excel/VBA and retrieving data from MSSQL Server DB RRS feed

  • Question

  • Hi all I'm in the process of learning VBA in Excel and would consider myself at an intermediate level. I'm now at the stage where I have been able to complete an Excel application that is embedded within SharePoint for data transfer/approval purposes. I would like to enhance the functionality of my application by having it import data from our MSSQL DB, at the point the user saves it back to the SharePoint server. I have done some research and have found the following guide for connecting to the DB and retrieving data using VBA: which I have tested successfully on my local machine with MSSQL Server 2012 Express installed. I am fairly confident with all the necessary SQL querying etc. as I do have experience with PHP/mySQL, but I do have a concern that the code in the above link exposes security risks, as it is not too difficult for the user to pull out the DB & Table names from the code. As we all know, Excel offers very little in terms of security. My question is, are there alternative methods for creating the necessary DB connection and querying the data, which would not expose any of the DB information to the users. Many thanks in advance for your input.
    Thursday, May 30, 2013 11:30 PM

All replies

  • I'm not sure knowing the table and server names helps anyone much, unless the workbook is visible over the Internet. SQL Server security will naturally limit all access based on its security model. If you use a login name and password rather than NT authentication, then access should again be limited by db security.

    I would worry more about SQL injection where users get to try queries in the db that might reveal more info. SQL injection is easy to prevent. Either use parameters all the time or make sure any data the user can enter (through inputboxes or Excel cells) is validated. Validate every cell you use in a dynamic query.

    For example I remove all end of statement and comment characters from any dynamically built queries. If you're not familiar with SQL injection risks, please search the internet.

    A workbook only accessible by internal staff is a different security risk to a workbook visible over the internet.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Friday, May 31, 2013 2:33 AM
  • Many thanks for he response, Rob. The more I think about it, the more I appreciate the validity of your comment. Using domain specific credentials for creating establishing the connection to the DB, the likelihood of any malicious injection is pretty slim. Not only that, but initial usage would require read only access, as the application will be retrieving data from the DB rather than writing to. Even with that in mind however, I am highly aware of the need to prevent SQL injection as a matter of course when writing any application, regardless of demographics. I have come from a background of a hobbyist PHP/mySQL developer and taking the step into MS application environment has been massive for me (especially considering my age). I have, as I say, established a foothold in VBA initially and am now ready to progress into C#, ASP & .NET, hoping of course that there will be enough familiarities with my former experience to reduce the learning curve somewhat. I am pleased to say that my experience with phpmyadmin has helped to make the initial steps in SQLServer Management Studio a little less daunting, enabling me to quickly throw together a functional DB. If you are able to recommend a book that teaches MSSQL management and administration, I would be eternally grateful. Regards CC
    Saturday, June 1, 2013 8:57 AM
  • I apologise for the wall of text by the way. It appears that when posting on the iPad, line-breaks do not seem to stand. CC
    Saturday, June 1, 2013 8:59 AM