Cannot perform a differential backup for database

hi seniors ,

I am running sql server 2005 under details

Microsoft SQL Server 2005 - 9.00.3033.00 (X64)

Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit)
on Windows NT 5.2 (Build 3790: Service Pack 1)

When i attempt to take differentail backup i receieved following message

"Cannot perform a differential backup for database "abc",
because a current database backup does not exist.
Perform a full database backup by reissuing BACKUP DATABASE,
omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035)
BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)."

when i search this msg on net than i find that it is microsoft bug 863 so i found hotfixes
about this problem and tried to install but unable to install (On installation screen select feature i am unable to click on check box)

Can any one help me regarding to this issue .And more is.. full back of db "abc" exists and i never changed its location nor rename file name.

Regards

First action of any backup activity should be full backup. For differential backup u should have a Baseline/Full backup. SO first take full backup and the try differential backup

backup database SomeDtabase to Disk='D:\Somefile.bak' with init

the above statment will create the full backup of your database. then do the diffrential backup . Read more in BOL

Madhu

|||

thanxs madhu , When i saw deatils about with INIT...I think it should work now. But one thing more wheather i should use this clasue with differential backup or not...

thanxs again

Ahmad Drshen

|||

it depends .... whether u need to overwrite the backup file or not... its again depend upon the Backup strategy of your server....

Read more about INIT in BOL

Madhu

|||

Hi Madhu K Nair and other Seinors ,

Agian , i am fall in problem

" Cannot perform a differential backup for database "AAA", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).

I have just Taken backup of db "AAA" on friday at 7:30 PM through script

BACKUP DATABASE AAA TO DISK = 'C:\LV_full_AAA_20071702.bkp'

WITH INIT , NOUNLOAD ,

NAME = 'AAAbackup', NOSKIP , STATS = 10,

NOFORMAT

DECLARE @.i INT

select @.i = position from msdb..backupset where database_name='AAA'

and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='AAA')

RESTORE VERIFYONLY FROM DISK = 'C:\LV_full_AAA_20071702.bkp' WITH FILE = @.i

Database backup has done successfully. :)

Again I ran the script to take differentail backup on friday at 11:30 PM through script

BACKUP DATABASE AAA TO DISK = 'C:\LV_Diff_AAA_20071702.bkp'

WITH INIT , NOUNLOAD , DIFFERENTIAL ,

NAME = 'AAAbackup', NOSKIP , STATS = 10,

NOFORMAT

DECLARE @.i INT

select @.i = position from msdb..backupset where database_name='AAA'

and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='AAA')

RESTORE VERIFYONLY FROM DISK = 'C:\LV_Diff_AAA_20071702.bkp' WITH FILE = @.i

Differentail backup has also done successfully.

But on saturaday when the script ran for taking differentail backup of db AAA on 11:30 PM

it repoted follwoing error

" Cannot perform a differential backup for database "AAA", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). "

while these script are working properly on another production server even there is not mention WITH INIT option with full backup.

wat the hell is going in taking backup my this server .One thing more i just installed the SP2 exe name was "SQLServer2005SP2-KB921896-x64-ENU.exe" on my server perhaps i am stucking with this problem after installing this plz let me know wat is the solution to fix this hell.

thanxs in advance

Ahmad Drshen

|||

How recent is your full backup of the database? Have there been any significant changes since then?

This is CTP2 for SP2. Did you enable the VarDecimal compression? This would invalidate the previous full, as the database version number is changed.

|||

thanxs ,VarDecimal compression ! as far as i know this is not supported in standard edtion of Sql server. any how i also try to enable this anf got the messahe that "

Vardecimal Storage Format is not supported on Standard Edition (64-bit).

"

I just tookfull backup on friday on 7:30 and then took differential backup on 11:30 on firday it happened successfully.

But on saturady i was unable to take differential backup again due to this error.

"Cannot perform a differential backup for database "abc",
because a current database backup does not exist.
Perform a full database backup by reissuing BACKUP DATABASE,
omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035)
BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)."

plz any other solution

|||

ooh i just solve it by the help of follwoing article

http://support.microsoft.com/kb/903643

|||

Hi there,

I've had a similar problem with Differential Backups for SQL Server however I am on SP2 for SQL Server 2005 and SP2 of Windows Server 2003. The differential backups work perfectly until an NTBACKUP is run with the snapn option so that it uses the Volume Shadow Copy. The next differential backup executed by SQL Server then fails with the same sorts of errors reported here in this thread. If I run NTBACKUP with snapff then the problem disappears and SQL Server differential backups carry on working as usual.

Kind Regards

|||

HI there specially Post21, u r quite right , this problem is due to Ntbackup utility but unluckly i change the option sanp oFF but still facing problem. can u guide me some Backup command switches which can help in this issue.

|||

I found the solution to my problem. I posted on a thread in Newsgroups on this but thought I'd post my findings here as well.

I couldn't understand why all of a sudden I was getting broken differential chains when NT BACKUP was run using volume shadow copy service after
upgrading the servers to SP2 of SQL Server 2005 and SP2 of Windows Server 2003. As in this KB http://support.microsoft.com/kb/903643. I hadn't had
this problem until this upgrade.

Here's the reason:

The "Readme for Microsoft SQL Server 2005 Service Pack 2" at
http://download.microsoft.com/download/2/b/5/2b5e5d37-9b17-423d-bc8f-b11ecd4195b4/ReadmeSQL2005SP2.htm
states:

3.2.6 SQL Server 2005 SP2 Changes the SQL Writer Service Default Startup Type and Default State

Note the following changes to the SQL Writer service default startup type and default state:

The SQL Writer service startup type is changed from Manual to Automatic.

The SQL Writer service default state is changed from Stopped to Started.

So I changed the service back to manual and stopped the service. This of course
behaved as expected, the VSS didn't try to lock my Full recovery model
databases and break the differential chain. I can still allow /volsnapn
option with my NTBACKUP commands to backup all the other "open" files on the
volume without breaking the database differential chain.

The thing that started me looking into this was this line in the BOL article
that a guy on the newsgroup mentioned.

This SQL Writer Service is automatically installed but not enabled by
default. It must be explicitly enabled to run on the server machine and must
be running at the time that the Volume Shadow Copy Service (VSS) application
requests a backup or restore. Use the Microsoft Windows Services applet to
configure the service. The SQL Writer Service installs on all operating
systems but is only needed on server systems. For Microsoft Windows XP, use
the MSDE Writer.

Hope the above helps somebody else out of the same situation.

Regards
Nick

|||thats quite fine and i have got the solution . thanks for all of participents.

Cannot perform a differential backup for database

hi seniors ,

I am running sql server 2005 under details

Microsoft SQL Server 2005 - 9.00.3033.00 (X64)

Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit)
on Windows NT 5.2 (Build 3790: Service Pack 1)

When i attempt to take differentail backup i receieved following message

"Cannot perform a differential backup for database "abc",
because a current database backup does not exist.
Perform a full database backup by reissuing BACKUP DATABASE,
omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035)
BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)."

when i search this msg on net than i find that it is microsoft bug 863 so i found hotfixes
about this problem and tried to install but unable to install (On installation screen select feature i am unable to click on check box)

Can any one help me regarding to this issue .And more is.. full back of db "abc" exists and i never changed its location nor rename file name.

Regards

First action of any backup activity should be full backup. For differential backup u should have a Baseline/Full backup. SO first take full backup and the try differential backup

backup database SomeDtabase to Disk='D:\Somefile.bak' with init

the above statment will create the full backup of your database. then do the diffrential backup . Read more in BOL

Madhu

|||

thanxs madhu , When i saw deatils about with INIT...I think it should work now. But one thing more wheather i should use this clasue with differential backup or not...

thanxs again

Ahmad Drshen

|||

it depends .... whether u need to overwrite the backup file or not... its again depend upon the Backup strategy of your server....

Read more about INIT in BOL

Madhu

|||

Hi Madhu K Nair and other Seinors ,

Agian , i am fall in problem

" Cannot perform a differential backup for database "AAA", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).

I have just Taken backup of db "AAA" on friday at 7:30 PM through script

BACKUP DATABASE AAA TO DISK = 'C:\LV_full_AAA_20071702.bkp'

WITH INIT , NOUNLOAD ,

NAME = 'AAAbackup', NOSKIP , STATS = 10,

NOFORMAT

DECLARE @.i INT

select @.i = position from msdb..backupset where database_name='AAA'

and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='AAA')

RESTORE VERIFYONLY FROM DISK = 'C:\LV_full_AAA_20071702.bkp' WITH FILE = @.i

Database backup has done successfully. :)

Again I ran the script to take differentail backup on friday at 11:30 PM through script

BACKUP DATABASE AAA TO DISK = 'C:\LV_Diff_AAA_20071702.bkp'

WITH INIT , NOUNLOAD , DIFFERENTIAL ,

NAME = 'AAAbackup', NOSKIP , STATS = 10,

NOFORMAT

DECLARE @.i INT

select @.i = position from msdb..backupset where database_name='AAA'

and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='AAA')

RESTORE VERIFYONLY FROM DISK = 'C:\LV_Diff_AAA_20071702.bkp' WITH FILE = @.i

Differentail backup has also done successfully.

But on saturaday when the script ran for taking differentail backup of db AAA on 11:30 PM

it repoted follwoing error

" Cannot perform a differential backup for database "AAA", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). "

while these script are working properly on another production server even there is not mention WITH INIT option with full backup.

wat the hell is going in taking backup my this server .One thing more i just installed the SP2 exe name was "SQLServer2005SP2-KB921896-x64-ENU.exe" on my server perhaps i am stucking with this problem after installing this plz let me know wat is the solution to fix this hell.

thanxs in advance

Ahmad Drshen

|||

How recent is your full backup of the database? Have there been any significant changes since then?

This is CTP2 for SP2. Did you enable the VarDecimal compression? This would invalidate the previous full, as the database version number is changed.

|||

thanxs ,VarDecimal compression ! as far as i know this is not supported in standard edtion of Sql server. any how i also try to enable this anf got the messahe that "

Vardecimal Storage Format is not supported on Standard Edition (64-bit).

"

I just tookfull backup on friday on 7:30 and then took differential backup on 11:30 on firday it happened successfully.

But on saturady i was unable to take differential backup again due to this error.

"Cannot perform a differential backup for database "abc",
because a current database backup does not exist.
Perform a full database backup by reissuing BACKUP DATABASE,
omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035)
BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)."

plz any other solution

|||

ooh i just solve it by the help of follwoing article

http://support.microsoft.com/kb/903643

|||

Hi there,

I've had a similar problem with Differential Backups for SQL Server however I am on SP2 for SQL Server 2005 and SP2 of Windows Server 2003. The differential backups work perfectly until an NTBACKUP is run with the snapn option so that it uses the Volume Shadow Copy. The next differential backup executed by SQL Server then fails with the same sorts of errors reported here in this thread. If I run NTBACKUP with snapff then the problem disappears and SQL Server differential backups carry on working as usual.

Kind Regards

|||

HI there specially Post21, u r quite right , this problem is due to Ntbackup utility but unluckly i change the option sanp oFF but still facing problem. can u guide me some Backup command switches which can help in this issue.

|||

I found the solution to my problem. I posted on a thread in Newsgroups on this but thought I'd post my findings here as well.

I couldn't understand why all of a sudden I was getting broken differential chains when NT BACKUP was run using volume shadow copy service after
upgrading the servers to SP2 of SQL Server 2005 and SP2 of Windows Server 2003. As in this KB http://support.microsoft.com/kb/903643. I hadn't had
this problem until this upgrade.

Here's the reason:

The "Readme for Microsoft SQL Server 2005 Service Pack 2" at
http://download.microsoft.com/download/2/b/5/2b5e5d37-9b17-423d-bc8f-b11ecd4195b4/ReadmeSQL2005SP2.htm
states:

3.2.6 SQL Server 2005 SP2 Changes the SQL Writer Service Default Startup Type and Default State

Note the following changes to the SQL Writer service default startup type and default state:

The SQL Writer service startup type is changed from Manual to Automatic.

The SQL Writer service default state is changed from Stopped to Started.

So I changed the service back to manual and stopped the service. This of course
behaved as expected, the VSS didn't try to lock my Full recovery model
databases and break the differential chain. I can still allow /volsnapn
option with my NTBACKUP commands to backup all the other "open" files on the
volume without breaking the database differential chain.

The thing that started me looking into this was this line in the BOL article
that a guy on the newsgroup mentioned.

This SQL Writer Service is automatically installed but not enabled by
default. It must be explicitly enabled to run on the server machine and must
be running at the time that the Volume Shadow Copy Service (VSS) application
requests a backup or restore. Use the Microsoft Windows Services applet to
configure the service. The SQL Writer Service installs on all operating
systems but is only needed on server systems. For Microsoft Windows XP, use
the MSDE Writer.

Hope the above helps somebody else out of the same situation.

Regards
Nick

|||thats quite fine and i have got the solution . thanks for all of participents.

Cannot Install Workstation Components - Missing Management Studio

Hello,

I have installed SQL Server 2K5 Developer Edition x64, when viewing the option under Start -> All Programs -> Microsoft SQL Server 2005, I do not have the option for Management Studio.

When I attempt to install workstation components I get a message claiming the components I am attempting to install are already installed on the machine and the install will not let me continue.

Any help would be appreciated.

Thanks,

CC

-

Further details, I installed SQL Server after .Net 2K5.

Once I removed all SQL Server components, then re-installed SQL Server 2K5 all appropriate tools were present.

CC

-

|||

While installing , to get tools, you need to select Workstation components from the same window where you sleect Services. If the services are already installed, then go to Control Pannel -- Add/Remove Prorgamm -- Sql server -- Change/Remove -- choose Change and select the Workstation components. It will install only Components. If you are not installed any services, then Install the services and worksation components ...

Madhu

|||

Thanks Madhu,

I resolved the issue prior to you posting by removing SQL Server and SQL Express (installed via .Net 2005 Install). Upon re-installing SQL Server 2005 Dev I was able to access SSMS. I will keep this in mind if I experience this behavior again

Thanks again,

CC

-

Cannot install SP1 on SQL Server 2005 (64bit)

Hi,

I'm having problems installing SP1 on one of our servers.

If I try to install the x64 version of SP1 setup says it can not update my server and I should use x86 version. Trying to install the x86 version results in the message I should use x64 version....

Any ideas?

Regards, Jan

What version of SQL do you have on the machine? x64 or x86? And when are you getting the error?

Thanks,
Sam Lester (MSFT)

|||

Hi Sam,

version is Microsoft SQL Server Standard Edition (64-bit). I get this error after doubleclicking the service pack .exe file.

Regards,

Jan

|||

OK, just to confirm what is on the box, can you go to Add/Remove Programs, click on change, and then on Report. This will show everything that the discovery process finds associated with SQL Server. This may help us figure out why the detection is giving you the error.

Also, can you supply the package name that you're trying to run?

Thanks,
Sam

|||

Hi Sam,

here is the report:

---

SIAM

The following components are installed on this server

MSSQLSERVER

Analysis Services

[Version: 9.00.1399.06 Edition: Standard Edition (64-bit) Patch level: 9.00.1399.06 Language: English (United States)]

Database Engine

[Version: 9.00.1399.06 Edition: Standard Edition (64-bit) Patch level: 9.00.1399.06 Language: English (United States)]

Common components

Integration Services

[Version: 9.00.1399.06 Edition: Standard Edition (64-bit) Patch level: 9.00.1399.06 Language: English (United States)]

Notification Services

[Version: 9.00.1399.06 Edition: Standard Edition (64-bit) Patch level: 9.00.1399.06 Language: English (United States)]

Workstation Components

[Version: 9.00.1399.06 Edition: Standard Edition (64-bit) Patch level: 9.00.1399.06 Language: English (United States)]

---

Regards,

Jan

|||

Great, thanks for posting the info! And to confirm, are you running this SP1 package:

SQLServer2005SP1-KB913090-x64-ENU.exe

from

http://www.microsoft.com/downloads/details.aspx?familyid=cb6c71ea-d649-47ff-9176-e7cac58fd4bc&displaylang=en

Thanks,
Sam

|||

Hi,

no I didn't run this package but SQLServer2005SP1-KB913090-x64-ENU.exe (most of our server are german version).

With the englisch version installation worked - thanks for the great support.

Anyway, the error message is a bit misleading...

Regards,

Jan

Cannot Install Reporting Services Components!

I have set up a Virtual Machine that runs Windows Server 2003 x64.
When I come to install SQL Server Express edition the Reporting
Services components don't even show up as options for installation.
Oh, I'm installing Express edition with advanced services by the way
and I have the 3.0 .NET services already in place. I don't have IIS
installed though, and I don't know if that is related (it's the only
warning I get during install). I can only see Client and Database
components in the install. I'm trying to create a virtualized
environment where I can do some testing and run the Adventure Works
DBs... that way I have a place to train using the Microsoft training
modules. Tht way, it will not affect any of the real databases we use.IIS has to be there. Reporting Services is an asp.net application. RS 2005
is a 2.0 framework application (so 3.0 framework is of no use to it).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ayman" <aymantg@.gmail.com> wrote in message
news:1180621469.669972.171680@.g4g2000hsf.googlegroups.com...
>I have set up a Virtual Machine that runs Windows Server 2003 x64.
> When I come to install SQL Server Express edition the Reporting
> Services components don't even show up as options for installation.
> Oh, I'm installing Express edition with advanced services by the way
> and I have the 3.0 .NET services already in place. I don't have IIS
> installed though, and I don't know if that is related (it's the only
> warning I get during install). I can only see Client and Database
> components in the install. I'm trying to create a virtualized
> environment where I can do some testing and run the Adventure Works
> DBs... that way I have a place to train using the Microsoft training
> modules. Tht way, it will not affect any of the real databases we use.
>|||On May 31, 10:34 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> IIS has to be there. Reporting Services is an asp.net application. RS 2005
> is a 2.0 framework application (so 3.0 framework is of no use to it).
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ayman" <ayma...@.gmail.com> wrote in message
> news:1180621469.669972.171680@.g4g2000hsf.googlegroups.com...
> >I have set up a Virtual Machine that runs Windows Server 2003 x64.
> > When I come to install SQL Server Express edition the Reporting
> > Services components don't even show up as options for installation.
> > Oh, I'm installing Express edition with advanced services by the way
> > and I have the 3.0 .NET services already in place. I don't have IIS
> > installed though, and I don't know if that is related (it's the only
> > warning I get during install). I can only see Client and Database
> > components in the install. I'm trying to create a virtualized
> > environment where I can do some testing and run the Adventure Works
> > DBs... that way I have a place to train using the Microsoft training
> > modules. Tht way, it will not affect any of the real databases we use.
Ok, so I'll install IIS before running SQL Server installation.
Correction, I installed 2.0 .NET framework then I installed the SDK
(300mb) version on after that. Is that going to be ok? 3.0 is the
Vista one, my mistake. Is the initial reason why RS does not show up
in the installation because IIS is not there? It's just invisible.|||The issue with the 2.0 framework is whether the website is setup to be
running the framework. IIS has to be there for RS to install.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ayman" <aymantg@.gmail.com> wrote in message
news:1180627922.532307.151760@.q66g2000hsg.googlegroups.com...
> On May 31, 10:34 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> IIS has to be there. Reporting Services is an asp.net application. RS
>> 2005
>> is a 2.0 framework application (so 3.0 framework is of no use to it).
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ayman" <ayma...@.gmail.com> wrote in message
>> news:1180621469.669972.171680@.g4g2000hsf.googlegroups.com...
>> >I have set up a Virtual Machine that runs Windows Server 2003 x64.
>> > When I come to install SQL Server Express edition the Reporting
>> > Services components don't even show up as options for installation.
>> > Oh, I'm installing Express edition with advanced services by the way
>> > and I have the 3.0 .NET services already in place. I don't have IIS
>> > installed though, and I don't know if that is related (it's the only
>> > warning I get during install). I can only see Client and Database
>> > components in the install. I'm trying to create a virtualized
>> > environment where I can do some testing and run the Adventure Works
>> > DBs... that way I have a place to train using the Microsoft training
>> > modules. Tht way, it will not affect any of the real databases we use.
> Ok, so I'll install IIS before running SQL Server installation.
> Correction, I installed 2.0 .NET framework then I installed the SDK
> (300mb) version on after that. Is that going to be ok? 3.0 is the
> Vista one, my mistake. Is the initial reason why RS does not show up
> in the installation because IIS is not there? It's just invisible.
>|||On May 31, 4:58 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> The issue with the 2.0 framework is whether the website is setup to be
> running the framework. IIS has to be there for RS to install.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ayman" <ayma...@.gmail.com> wrote in message
> news:1180627922.532307.151760@.q66g2000hsg.googlegroups.com...
> > On May 31, 10:34 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> > wrote:
> >> IIS has to be there. Reporting Services is an asp.net application. RS
> >> 2005
> >> is a 2.0 framework application (so 3.0 framework is of no use to it).
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >> "Ayman" <ayma...@.gmail.com> wrote in message
> >>news:1180621469.669972.171680@.g4g2000hsf.googlegroups.com...
> >> >I have set up a Virtual Machine that runs Windows Server 2003 x64.
> >> > When I come to install SQL Server Express edition the Reporting
> >> > Services components don't even show up as options for installation.
> >> > Oh, I'm installing Express edition with advanced services by the way
> >> > and I have the 3.0 .NET services already in place. I don't have IIS
> >> > installed though, and I don't know if that is related (it's the only
> >> > warning I get during install). I can only see Client and Database
> >> > components in the install. I'm trying to create a virtualized
> >> > environment where I can do some testing and run the Adventure Works
> >> > DBs... that way I have a place to train using the Microsoft training
> >> > modules. Tht way, it will not affect any of the real databases we use.
> > Ok, so I'll install IIS before running SQL Server installation.
> > Correction, I installed 2.0 .NET framework then I installed the SDK
> > (300mb) version on after that. Is that going to be ok? 3.0 is the
> > Vista one, my mistake. Is the initial reason why RS does not show up
> > in the installation because IIS is not there? It's just invisible.
Ok I got it working. I had to setup up IIS and then make it so that
it will work in 32-bit mode. Afterwards it started working. I'm
thinking I could have just put the 180 day demo version on instead of
the express edition since it has more functionality. Oh well.

Cannot in install samples.

When I try to install the x64 version of the AdventureWorks sample database I get an error indicating that the file is corrupt (data1.cab). Is there another version or somewhere else I can go to download the sample .msi files? Currently I am going to http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en and downloading SqlServerSamples_x64.msi and AdventureWorksDB_x64.msi.

Thank you.

Kevin Burton

rkevinburton@.charter.net

Hi Kevin,

You're right, there is a problem with that .msi file. We're working on a fix this week and should have the site updated with the corrected file in a few days. For more information, see the Sample blog on the same subject here: http://blogs.msdn.com/bonniefe/archive/2006/04/21/580777.aspx#comments

Regards,

Gail

|||

This problem has been fixed. Please give it another try.

Cannot generate SSI Context

New install of SQL Server 2005 (x64) Standard after uninstalling previous version SQL Server (x86) Standard.

After installation, client connections are receiving "Cannot generate SSI Context" error messages. TCP/IP Integrated Authentication is being used.

Kerboros authentication is failing when SQLService is running under a domain account and works when running under local system account by reverting back to NTLM authentication.

How do I get the Server to revert to NTLM authentication when running under a domain account so that the error will go away?

Mike

First, here are great blogs for troubleshooting this problem:

http://blogs.msdn.com/sql_protocols/archive/2005/10/15/481297.aspx

http://blogs.msdn.com/sql_protocols/archive/2005/10/19/482782.aspx

Secondly:

Are you making local connection or remote connection?

If local, do not use protocol prefix like "tcp:" in your connection string. BTW, by OS default, local authentication always use NTLM except on XP.

If remote, use "np:" explicitly in your connection string which requires rmote named pipe enabled.

Finally:

Use tool Setspn.exe -D <spn> "computername" to delete SPN you registered, without spn, kerbroes would not take effect.

HTH

Ming.

|||

I have read these two blogs. My question is how do I get the client to automatically revert to NTLM when using a domain account for the service without receiving the error.

I am making remote connections from SQL Server Studio with both XP and Server 2003 (both display the same behavior), and Remote Connections are enabled on the Server. There is not an actual connection string with a specific protocol prefix, it always uses the default I assume.

We are required to always use TCP/IP and integrated authentication here.

It appears that there is an orphan spn that is causing my problem. If I delete it, this should cause the system to revert to use NTLM and I should be able to change the service to run under an account that is not a Domain administrator, correct?

Thanks for your assistance.

Mike

|||

If a SPN is registered for the SQL Server service, client will find the SPN and Kerboros will be used. If SPN is not registered, client will automatically revert to NTLM. SPN registration/deregistration is done automatically by the SQL Server, a client cannot choose to use or not use it.

Are you sure NTLM is used when you run the server under local system account? please use the following command to confirm it: "select auth_scheme from sys.dm_exec_connections where session_id=@.@.spid "

Several questions:

1) did you gracefully shut down SQL Server (x86) before you uninstall it?

2) Can you stop you sql server and run "setspn -L yourmachinename"? Do you see "MSSQLSvc/....."? how about you run the command when sql server is running under local system and/or a domain account?

Most likely, you did not cleanly uninstall the old sql server and a SPN is left over. Please try to delete it as suggested by Ming LV.

Thanks,

|||

The Active Directory admin has deleted the SPN for this server.

I am still unable to connect to the Server from the SQL 2005 client. The samne error message appears. Next we will wipe out the machine and try a clean install I guess.

|||

1) On your server box, use "Setspn -L <machinename> " check whether SPN like" MSSQLSvc/<your machine FQDN>:1433" goes away.

2) Shutdown your sql server, change the service account to a non-admin account, restart your sql server.

3) Make remote tcp connection.

BTW, sql server does not force kerberoes, it is OS behavior, if OS can not find a SPN in KDC for the server, it would automatically fall back to NTLM.

Thanks!

Ming.

|||

Assuming that (1) your SPN is removed and (2) you are doing remote connection, (3) you were testing connection without rebooting the client machine after removing the SPN in domain controler/AD,

One possibility is that the cached kerberos "tickets" haven't expired yet in your client machine. You can either reboot your client machine or use "klist.exe -purge" to clean the kerberos cache. "klist.exe" comes with w2k3 resource kits.

Running under domain account is recommened. Reinstalling the sql server might help in other cases but certainly not in this situation.

|||

My System Administrator assured me that he deleted the SPN.

This server does not have a copy of setspn on it. Do you know where I can find this utility?

I rebooted the client but am still receiving the saame message.

Funny thing is the machine right next to this one is running SQL Server 2005 withouyt any problem running under the same domain account with identical permissions.

Reinstalling the operating system Windows Server 2003 X64 Standard and reinstalling SQL Server X64 Standard will not help either?

Here is the actual error I am receiving:

Server Name: CARTLABSVR2
Error Number: 0
Severity: 11
State: 0
Procedure: GenClientContext

Program Location:

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.SSPIError(String error, String procedure)
at System.Data.SqlClient.TdsParser.SNISSPIData(Byte[] receivedBuff, UInt32 receivedLength, Byte[] sendBuff, UInt32& sendLength)
at System.Data.SqlClient.TdsParser.ProcessSSPI(Int32 receivedLength)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

|||

You can get setspn tool from w2k3 resource kit.

Can you test the following.

(1) change your sql serverb service to localsystem account.

(2) restart the service.

(3) test the connection and let me what you get.

(4) shutdown the service cleanly.

(5) restart the server under the domain account you are using now.

(6) test the conenction and let me know what you get.

Can you also post the errorlog info w.r.t to SPN?

|||

When I change the service to use Local System, it works.

When I change it back to use the domain account, it does not work.

Here is the error message that shows up in the error log when I chaged the account the service runs under:

The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

Version: Microsoft SQL Server 2005 - 9.00.1399.06 (X64)

Oct 14 2005 00:35:21

Copyright (c) 1988-2005 Microsoft Corporation

Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

Authentication mode is MIXED.

On uninstall:

The SQL Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098. Administrator should deregister this SPN manually to avoid client authentication errors.

|||

"The SQL Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098. Administrator should deregister this SPN manually to avoid client authentication errors."

Do you also see this message showing up in the shutdown of the sql service under "localsystem" or "domain account"? You shouldn't normally.

So, I need to you have setspn.exe to to verity if you have a orphan spn registried. Klist.exe to verify what you have in your client ticket cache, You can get them from resource kit of sql server 2003.

|||

I downloaded setspn and ran it with the -l flag. The Server name is registered as expected but there are no other listings for the SQLServerSrvc. So an invalid SPN apparently does not exist currently.

|||can you try klist.exe purge and type yes for all. Try to connect. Let me know what you get?|||

The SPN error does not show up when I change form the Local System account.

KList shows this ticket from my client workstation:

Server: MSSQLSvc/cartlabsvr2.ad.garmin.com:1433@.AD.GARMIN.COM
KerbTicket Encryption Type: RSADSI RC4-HMAC(NT)
End Time: 5/1/2006 23:30:17
Renew Time: 5/8/2006 13:30:17

Is this what is causing the problem? How do I get rid if this?

II also asked a co-worker to try to connect that has not connected to this SQL Server previously, and he received the same SSIS Context error....

|||

"klist.exe purge" should clean up your local cache.

As you can see,

KerbTicket Encryption Type: RSADSI RC4-HMAC(NT)
End Time: 5/1/2006 23:30:17 <<< the cache is created just now.
Renew Time: 5/8/2006 13:30:17 <<<< the cache will only expire after a week. The AD policy does not expect you to change account very often.

Let me what you get after you purge off all the ticket. If it failed again, I hope not, let me know what shows up in setspn.exe and klist.exe.