Copy Database Wizard on Vista x64 machine

29 Nov 2007 5:02 PM

CJM

I've got a new Vista x64 build with SQL2005 (+SP2) freshly installed. I'm
now trying to copy existing databased from other services but I can't get it
to work. I get the following error logged in the Event Viewer Application
Log:

"Log Name:      Application
Source:        Application Error
Date:          29/11/2007 16:16:56
Event ID:      1000
Task Category: (100)
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      mycomputer
Description:
The description for Event ID 1000 from source Application Error cannot be
found. Either the component that raises this event is not installed on your
local computer or the installation is corrupted. You can install or repair
the component on the local computer.

If the event originated on another computer, the display information had to
be saved with the event.

The following information was included with the event:

DTExec.exe
2005.90.3042.0
45cd7539
kernel32.dll
6.0.6000.16386
4549d328
e053534f
00000000000233ac

The substitution string for insert index (%1) could not be found"

I cant find anything online that enlightens me - I know that Vista needs SP
installed to work, so I've installed it:

@@Version=
Microsoft SQL Server 2005 - 9.00.3054.00 (X64)
Mar 23 2007 18:41:50
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.0 (Build 6000: )

All the services are running under a domain user that has access to the
areas where the data is stored, both source and destination, so I'm hoping
it's not a permissions issue - if it is, I wouldnt know where to look.

I *could* copy a local database to the same server (renamed of course), so
it suggests that the server is largely OK - does this suggest it *is* a
permissions issue?

The domain user that the services are running under are not in the local
Administrators group, but they are in the
SQLServer2005MSSQLUser$MyComputer$MSSQLSERVER group - which I assume is the
group that was set up by the SQL Server Provisioning Tool for Vista.

This is a development machine so I frequently want to copy different
versions of different databases backwards and forwards.

Any suggestions? Any other Vista x64-ers out there?

Thanks in advance...

Chris

29 Nov 2007 9:14 PM

Andy

SQL Server uses Active Directory accounts and groups for its
permissions.

Active Directory accounts and groups are represented by GUIDs that are
unique to a particular Active Directory Domain which is run by a
Primary Domain Contoroller. If your new Vista machine is not part of
a network, or is on a different domain than the old one, then it
probably uses different GUIDs for the active directory accounts than
the source machine did. If you copied over the SQL Server database
using a backup, then you probably also copied all the old GUIDs for
the SQL Server accounts that point to the GUIDs on the old machine,
and not the ones on new machine.

This means that your SQL Server accounts are out of sync with active
directory. you may have to recreate your SQL Server accounts on the
new machine.

Permissions are assigned through SQL Server Management Studio in two
steps. First, you assign the active directory accounts that are
allowed to connect to a SQL Server instance. This is done by right
clicking the Logins folder under the Security folder of the SQL Server
instance the user is to connect to and selecting New Login.

Then, you assign what SQL Server instance users are allowed to connect
to a particular database within an instance. This is done by right
clicking the Security folder under the database you want to give
permission for and selecting New->user.

In both cases, an extensive permissions dialog will open. For the
Login folder, you only have to specify an active directory account.
For the Security folder, you have to click securables in the left
sidebar, add the entities you want to view (tables), and then set
select, reference, update, delete, and insert permissions. There are
more steps, but this should point you in the right direction.