Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

cannot find database Expand / Collapse
Author
Message
Posted Friday, August 8, 2014 7:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 18, 2014 6:36 AM
Points: 6, Visits: 16
I had to database one on my local machine (LM) and one on the network machine (NM).

NM had a website where users and had to login to access the data using SQL authentication.


LM uses windows authentication. I updated LM made a backup.

detach NM and delete it. I copied the backup to NW and then Attach it.


now when users login they are seeing the message can't find database.

Even if I know for a fact the database is there with the same name.

Not sure what to do.
Post #1601187
Posted Friday, August 8, 2014 8:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 6,879, Visits: 13,459
Do both databases have the same name?
How can you connect to the DB restored on the NM (Mixed Mode or Windwos authentication only)?
In case of mixed mode: Did you add all SQL user from the original db to the new db? If not, the SQL login most probably won't work anymore....




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1601197
Posted Friday, August 8, 2014 8:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 18, 2014 6:36 AM
Points: 6, Visits: 16
both databases has the same name

the users are in the configuration file on the network website




<add name="DefaultConnection" connectionString="Data Source=xxxx;Initial Catalog=aspnet-cxcDB-20130627224632.mdf;User ID=xxxx;Password=xxxxx" providerName="System.Data.SqlClient" />
<add name="CXCDBConnectionString1" connectionString="Data Source=xxxx;Initial Catalog=CXCDB;User ID=xxxx;Password=xxxx" providerName="System.Data.SqlClient" />

[Edited by Administrator]
Post #1601198
Posted Friday, August 8, 2014 8:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 6,879, Visits: 13,459
earlcools (8/8/2014)
both databases has the same name

the users are in the configuration file on the network website




<add name="DefaultConnection" connectionString="Data Source=xxxxx;Initial Catalog=aspnet-cxcDB-20130627224632.mdf;User ID=xxxxxx;Password=xxxxx" providerName="System.Data.SqlClient" />
<add name="CXCDBConnectionString1" connectionString="Data Source=xxxx;Initial Catalog=CXCDB;User ID=xxxxx;Password=xxx" providerName="System.Data.SqlClient" />


I URGENTLY REQUEST YOU to remove the real connection string immediately from your post!!!
Obfuscate it, if needed!
Otherwise you'll be in BIG TROUBLE in almost no time!!!!




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1601206
Posted Friday, August 8, 2014 9:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 18, 2014 6:36 AM
Points: 6, Visits: 16
thanks
Post #1601247
Posted Friday, August 8, 2014 10:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 6,879, Visits: 13,459
I've seen you're using the most dangerous connection string possible.

This is absolutely the wrong way to connect to a database.

Create a SQL login limited to the database the application needs access to. Create a group within the database and put that SQL login into that group.

Allow only access to the group and the stuff that's really needed. E.g., specifically name the tables/views the app has SELECT permissions and/or the sprocs the app is allowed to execute.
Once you're able to connect to the database using Management Studio and the login you just created, change the connection string to the new login.

Another option:
If this is an Intranet solution, you could also change the connection string to use a trusted connection (=Windows authentication).
Ask your domain admins to create an AD group for users that are allowed to use the application.
At the SQL Server side, add the AD group to the SQL group and you should be all set.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1601261
Posted Friday, August 8, 2014 4:43 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 19, 2014 10:30 AM
Points: 322, Visits: 983
Even if the new server has all the sql logins on it, the SIDs of the users in the database are different, because the logins were created on a different server.

In the attached database, run:
ALTER USER UserName WITH LOGIN = LoginName
for each user associated with a sql login
Post #1601350
Posted Friday, August 8, 2014 4:43 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 19, 2014 10:30 AM
Points: 322, Visits: 983
Her is a nice background on the issue;
http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2013/10/09/i-ve-refreshed-a-test-database-from-prod-but-now-the-users-can-t-access-it.aspx
Post #1601351
Posted Monday, August 11, 2014 1:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 18, 2014 6:36 AM
Points: 6, Visits: 16
ALTER USER UserName WITH LOGIN = LoginName

I am sorry I could not get it to work.

A little more explanation please.
Post #1602035
Posted Monday, August 11, 2014 2:28 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:26 PM
Points: 2,355, Visits: 3,537
First, you need to create all the logins on NM that are used in the db you copied there. You can script out the logins from the old server, but you'll need to correct the password and remove any "DISABLE" login statements. [You can add the login's current SID on the CREATE LOGIN statement if you'll need to keep going from one server to another, as that will making sync'ing automatic.]

Then, if you didn't copy the existing SIDs, you'll need to run the ALTER USER command for every user to re-sync it to its login.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1602058
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse