Sign in Register
Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Mindaugas 6 posts 26 karma points
    Nov 23, 2015 @ 15:08
    Mindaugas
    0

    Change SQL database from server to local

    Hello,

    I am trying to switch my SQL database from one on server to local replica of another one. In my SQL Server Manager studio I've created and restored DB from file and fixed user settings. In my umbraco instalation I have connection String that looks like this:

    <connectionStrings>
    <remove name="umbracoDbDSN" />
    <add name="umbracoDbDSN" connectionString="server=PCMINJACL2;database=MySites;user id=MySites_user;password=password" providerName="System.Data.SqlClient" />
  </connectionStrings>

    I am not sure if I am missing something, because everytime I try to open the site I get this error message:

    Umbraco cannot start. A connection string is configured but the Umbraco cannot connect to the database.

    Any suggestions what it can be, because everything from server name to user to password are set to correct ones. So, I have no idea why it is not working. Thanks in forward for any help.

    Copy Link
  • Alex Skrypnyk 6162 posts 24132 karma points MVP 8x admin c-trib
    Nov 23, 2015 @ 15:17
    Alex Skrypnyk
    0

    Hi Mindaugas,

    As I know, you have to specify sql server what you are trying to connect.

    server=PCMINJACL2\sql2012

    Thanks

    Copy Link
  • Mindaugas 6 posts 26 karma points
    Nov 23, 2015 @ 15:35
    Mindaugas
    0

    enter image description here

    I thought all I need is what's marked as "Server name" in the pic? I tried adding "\sql2012" - no luck. I am not sure if I need to add windows user before, o anything else after this name. And if I am supposed to find that in SQL manager than I have no clue where as it is my first interactions with SQL.

    Copy Link
  • Alex Skrypnyk 6162 posts 24132 karma points MVP 8x admin c-trib
    Nov 23, 2015 @ 16:05
    Alex Skrypnyk
    0

    Mindaugas, if you are using windows authentication, you have to use connection string -

     connectionString="data source=PCMINJACL2;
   initial catalog=MySites;persist security info=True; 
   Integrated Security=SSPI;"

    Thanks

    Copy Link
  • Mindaugas 6 posts 26 karma points
    Nov 26, 2015 @ 14:46
    Mindaugas
    0

    Hi! Thanks for all the suggestions. Issue was that I was using backup of DB with SQL server user set and my local SQL properties were set to accept only windows authentication. Therefore Umbraco was throwing error when connecting with windows authentication to DB with user set on it and also I was receiving error with SQL user credentials, because SQL was not accepting anything but windows authentication. Once I changed SQL Managament Studio settings to allow SQL user connection everything was okay.

    Notice: it is easy to check if you are connecting to DB with SQL server user using Visaual Studio > Server explorer. You simply click right(second) mouse button on "Data Connection" and select "Add connection" > Select Server by name > select "SQL server Authentication" > Enter user id and user > Select database > Connect. If it connects then credentials and settings on SQL Studio are okay and under "Data Connections" you will have connection that you just added, by clicking on it and checking "Properties" you get connectionString which you can use in your web.config(only password might be hidden under "dots", so this one you will have to replace "dots" to actual password by hand).

    Hope my explanation is not to confusing. :)

    Copy Link
Please Sign in or register to post replies

Write your reply to:

Draft