none
Specifying a SQL login/user for a SQL Express database.

    Question

  • I've been trying to figure out how to include a SQL Server 2008 Express database in a deployment via ClickOnce, from VS 2010, where Windows Authentication is used to access the database.  I had thought it would work fine, but I've had a bear of a time adding the SQL Express database to my test VS 2010 project.  And I've asked on this forum, and have gotten some help, but it still hasn't solved my problem.  Simply put, VS 2010 will not let me add a SQL Express database to a project, if the connection uses Windows Authentication.

    Well, I don't have loads of time to try and resolve this, so I'm going to ask for help approaching this differently.  I'll try adding a SQL login and user to the SQL Express database.  I think I'll have it much easier doing it that way, at least being able to add the SQL Express database to the VS 2010 project as a data source.  What I don't know how to do is how to add that SQL user/login to a ClickOnce deployment.  How do I do that?


    Rod
    Tuesday, May 31, 2011 8:54 PM

Answers

  • Hi Rod,

    You need to add the database to your Visual Studio application, set the build action to "content" and set "copy to output directory" as "copy if newer". Then the database will be included in the application.

    By default, the connection string will use the DataDirectory property of the ApplicationDeployment.

    Then ClickOnce will publish the database to the data directory on the user machine. When there is an update, it will copy the database to a folder under the data folder called .\pre and put the new one in the top folder, and you will have to handle moving the data in code.

    This is obviously a p.i.t.a., and not something I recommend doing. For one thing, if the time stamp on the database changes, even if it's just from you checking out a table definition, ClickOnce will assume you changed it and redeploy it, even though you didn't expect it to do so.

    For this reason, I recommend moving it to a folder under ApplicationData after it is deployed, and managing the updates yourself. Here is an article that explains how to do this:

    http://robindotnet.wordpress.com/2009/08/19/where-do-i-put-my-data-to-keep-it-safe-from-clickonce-updates/

    You will also need to check the Application Files dialog and change it from "Include(Data)" to just "Include" so it no longer publishes it to the data folder, or change the code in that article to access it in the DataDirectory when copying it the first time.

    I'm not sure what problem you are having publishing the database and using windows authentication. It should work okay; you need to be sure you add the user to the database before you publish it. If you use a SQL username and password, be sure not to put them in the config file unless you encrypt them, or someone can easily find that file and check it out in notepad.

    Please post back more details if you need more help.

    RobinDotNet


    Click here to visit my ClickOnce blog!
    Microsoft MVP, Client App Dev
    • Marked as answer by Rod at Work Tuesday, June 07, 2011 8:36 PM
    Sunday, June 05, 2011 8:45 PM
    Moderator

All replies

  • Hi Rod,

    --> SQL Server 2008 Express database

    What do you want to be deployed with the application, database file or the Sql Server 2008 Express software? Please let us clear about your request, since they're totally different requests.

    And I think there's enough informations in that thread needed to deploy the application with a database file.

    I have the following options can help you to deploy a application with database file, and those options answered much developers.

    how to attach sql database file to creating .exe setup in c#

    I think you just can use the SmoApplication.EnumAvailableSqlServers method or SqlDataSourceEnumerator.GetDataSources method to help you collect the instance on the system, and then you can display them to the user on the Form, then let us select which instance they will use with your application.

    After those steps, you can make a correct connection string for the current user. Then your application can use this connection string in feature.

    And also this this thread:

    http://social.msdn.microsoft.com/Forums/en-US/winformssetup/thread/933da6c0-3564-4ad1-9e50-02804f7f7c4d/

    Check the database instance and then execute the sql script.

    And I do not think so those options would be affect under Windows Authentication and Sql Server Authentication.

    If there's any concern, please feel free to let us know.

    Best wishes,


    Mike [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, June 01, 2011 7:20 AM
    Moderator
  • Hi Rod,
    I am writing to check the status of the issue on your side. 
    What about this problem now? 
    Would you mind letting us know the result of the suggestions?
    Best wishes,

    Mike [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, June 03, 2011 5:27 AM
    Moderator
  • Hi Rod,

    You need to add the database to your Visual Studio application, set the build action to "content" and set "copy to output directory" as "copy if newer". Then the database will be included in the application.

    By default, the connection string will use the DataDirectory property of the ApplicationDeployment.

    Then ClickOnce will publish the database to the data directory on the user machine. When there is an update, it will copy the database to a folder under the data folder called .\pre and put the new one in the top folder, and you will have to handle moving the data in code.

    This is obviously a p.i.t.a., and not something I recommend doing. For one thing, if the time stamp on the database changes, even if it's just from you checking out a table definition, ClickOnce will assume you changed it and redeploy it, even though you didn't expect it to do so.

    For this reason, I recommend moving it to a folder under ApplicationData after it is deployed, and managing the updates yourself. Here is an article that explains how to do this:

    http://robindotnet.wordpress.com/2009/08/19/where-do-i-put-my-data-to-keep-it-safe-from-clickonce-updates/

    You will also need to check the Application Files dialog and change it from "Include(Data)" to just "Include" so it no longer publishes it to the data folder, or change the code in that article to access it in the DataDirectory when copying it the first time.

    I'm not sure what problem you are having publishing the database and using windows authentication. It should work okay; you need to be sure you add the user to the database before you publish it. If you use a SQL username and password, be sure not to put them in the config file unless you encrypt them, or someone can easily find that file and check it out in notepad.

    Please post back more details if you need more help.

    RobinDotNet


    Click here to visit my ClickOnce blog!
    Microsoft MVP, Client App Dev
    • Marked as answer by Rod at Work Tuesday, June 07, 2011 8:36 PM
    Sunday, June 05, 2011 8:45 PM
    Moderator