Error 823: SQL service won't start!

Hello all,
Our database server went down due to a power outage but everything came back
fine except the MSSQL serverice won't start. I get the following error
listed below when trying to start the service. I tried starting the service
with the -f switch, which didn't work. I also tried the rebuildm.exe which
also failed. Would installing SQL to different drive be worth the effort?
Thank you for any advice!
Troy
spid5 Clearing tempdb database.
spid5 Encountered an unexpected error while checking the sector size for
file 'C:\Program Files\Microsoft SQL Server\MSSQL\DATA\TEMPDB.MDF'. Check
the SQL Server error log for more information.
spid5 CREATE DATABASE failed. Some file names listed could not be created.
Check previous errors.
spid5 Error: 823, Severity: 24, State: 6
spid5 I/O error 38(Reached the end of the file.) detected during read at
offset 0000000000000000 in file 'C:\Program Files\Microsoft SQL
Server\MSSQL\DATA\TEMPDB.MDF'.
spid5 WARNING: problem activating all tempdb files. See previous errors.
Restart server with -f to correct the situation.Your disk has a fatal error and you will not be able to start the server as
the disk is where it's trying to create the TEMPDB database, which is
required for the server to startup. You should contact PSS who will be able
to help you (http://support.microsoft.com)
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Travis" <Tkasch@.neb.rr.com> wrote in message
news:xzL%b.15820$QP.6929@.twister.rdc-kc.rr.com...
> Hello all,
> Our database server went down due to a power outage but everything came
back
> fine except the MSSQL serverice won't start. I get the following error
> listed below when trying to start the service. I tried starting the
service
> with the -f switch, which didn't work. I also tried the rebuildm.exe
which
> also failed. Would installing SQL to different drive be worth the effort?
> Thank you for any advice!
> Troy
> spid5 Clearing tempdb database.
> spid5 Encountered an unexpected error while checking the sector size for
> file 'C:\Program Files\Microsoft SQL Server\MSSQL\DATA\TEMPDB.MDF'. Check
> the SQL Server error log for more information.
> spid5 CREATE DATABASE failed. Some file names listed could not be created.
> Check previous errors.
> spid5 Error: 823, Severity: 24, State: 6
> spid5 I/O error 38(Reached the end of the file.) detected during read at
> offset 0000000000000000 in file 'C:\Program Files\Microsoft SQL
> Server\MSSQL\DATA\TEMPDB.MDF'.
> spid5 WARNING: problem activating all tempdb files. See previous errors.
> Restart server with -f to correct the situation.
>

Error 823: SQL service won't start!

Hello all,
Our database server went down due to a power outage but everything came back
fine except the MSSQL serverice won't start. I get the following error
listed below when trying to start the service. I tried starting the service
with the -f switch, which didn't work. I also tried the rebuildm.exe which
also failed. Would installing SQL to different drive be worth the effort?
Thank you for any advice!
Troy
spid5 Clearing tempdb database.
spid5 Encountered an unexpected error while checking the sector size for
file 'C:\Program Files\Microsoft SQL Server\MSSQL\DATA\TEMPDB.MDF'. Check
the SQL Server error log for more information.
spid5 CREATE DATABASE failed. Some file names listed could not be created.
Check previous errors.
spid5 Error: 823, Severity: 24, State: 6
spid5 I/O error 38(Reached the end of the file.) detected during read at
offset 0000000000000000 in file 'C:\Program Files\Microsoft SQL
Server\MSSQL\DATA\TEMPDB.MDF'.
spid5 WARNING: problem activating all tempdb files. See previous errors.
Restart server with -f to correct the situation.Your disk has a fatal error and you will not be able to start the server as
the disk is where it's trying to create the TEMPDB database, which is
required for the server to startup. You should contact PSS who will be able
to help you (http://support.microsoft.com)
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Travis" <Tkasch@.neb.rr.com> wrote in message
news:xzL%b.15820$QP.6929@.twister.rdc-kc.rr.com...
> Hello all,
> Our database server went down due to a power outage but everything came
back
> fine except the MSSQL serverice won't start. I get the following error
> listed below when trying to start the service. I tried starting the
service
> with the -f switch, which didn't work. I also tried the rebuildm.exe
which
> also failed. Would installing SQL to different drive be worth the effort?
> Thank you for any advice!
> Troy
> spid5 Clearing tempdb database.
> spid5 Encountered an unexpected error while checking the sector size for
> file 'C:\Program Files\Microsoft SQL Server\MSSQL\DATA\TEMPDB.MDF'. Check
> the SQL Server error log for more information.
> spid5 CREATE DATABASE failed. Some file names listed could not be created.
> Check previous errors.
> spid5 Error: 823, Severity: 24, State: 6
> spid5 I/O error 38(Reached the end of the file.) detected during read at
> offset 0000000000000000 in file 'C:\Program Files\Microsoft SQL
> Server\MSSQL\DATA\TEMPDB.MDF'.
> spid5 WARNING: problem activating all tempdb files. See previous errors.
> Restart server with -f to correct the situation.
>

Error 53: Cannot generate SSPI Context

SQLserveragent service for SQL2K does not start and returns the following
error in the agent out file.
Error 53, Cannot generate SSPI Context
The agent was working until last week.
We have researched this error on Microsoft knowldge base and several news
groups and none of the stuff we read seems to work.
We CAN connect to the DB using the full DNS name or IP. But when trying to
use (local), the error appears. It just seems like SQL/Windows lost how to
find or resolve (local).
How does (local) work? This is happening on two servers in the same Active
Directory Win2k server domain. Could it be a domain level policy that is
goofed?
Thanks for any help.
"kenwu" <kenwu@.discussions.microsoft.com> wrote in message
news:61B5A8F9-AB41-4192-80D6-F9B96224DBF4@.microsoft.com...
> SQLserveragent service for SQL2K does not start and returns the following
> error in the agent out file.
> Error 53, Cannot generate SSPI Context
> The agent was working until last week.
> We have researched this error on Microsoft knowldge base and several news
> groups and none of the stuff we read seems to work.
> We CAN connect to the DB using the full DNS name or IP. But when trying
to
> use (local), the error appears. It just seems like SQL/Windows lost how
to
> find or resolve (local).
> How does (local) work? This is happening on two servers in the same
Active
> Directory Win2k server domain. Could it be a domain level policy that is
> goofed?
> Thanks for any help.
|||need to try to connect to sqlagent using sa account and password. you can do
that with the
sqlagen manager, change authentication and ener account and password, then
restart sqlserver agent. (there is a known kba for this problem sorry I
cannot find it now)
I will then suggest re-booting the server.
I vaguely remember it being something to do with the sqlagent service
accounts not being able to communicate with the domain controller and there
fore not being authenticated,causing the error.
"kenwu" <kenwu@.discussions.microsoft.com> wrote in message
news:61B5A8F9-AB41-4192-80D6-F9B96224DBF4@.microsoft.com...
> SQLserveragent service for SQL2K does not start and returns the following
> error in the agent out file.
> Error 53, Cannot generate SSPI Context
> The agent was working until last week.
> We have researched this error on Microsoft knowldge base and several news
> groups and none of the stuff we read seems to work.
> We CAN connect to the DB using the full DNS name or IP. But when trying
to
> use (local), the error appears. It just seems like SQL/Windows lost how
to
> find or resolve (local).
> How does (local) work? This is happening on two servers in the same
Active
> Directory Win2k server domain. Could it be a domain level policy that is
> goofed?
> Thanks for any help.
|||Hi Kenwu,
Look at this kbs:
http://support.microsoft.com/?id=814401
http://support.microsoft.com/?id=811889
http://dotnetjunkies.com/WebLog/pete...2/25/7897.aspx
Good Luck !
Rodrigo Fernandes
MCSE/MCDBA
"kenwu" wrote:

> SQLserveragent service for SQL2K does not start and returns the following
> error in the agent out file.
> Error 53, Cannot generate SSPI Context
> The agent was working until last week.
> We have researched this error on Microsoft knowldge base and several news
> groups and none of the stuff we read seems to work.
> We CAN connect to the DB using the full DNS name or IP. But when trying to
> use (local), the error appears. It just seems like SQL/Windows lost how to
> find or resolve (local).
> How does (local) work? This is happening on two servers in the same Active
> Directory Win2k server domain. Could it be a domain level policy that is
> goofed?
> Thanks for any help.
|||What changes you did last week?
Regards,
Daniel
"kenwu" <kenwu@.discussions.microsoft.com> wrote in message
news:61B5A8F9-AB41-4192-80D6-F9B96224DBF4@.microsoft.com...
> SQLserveragent service for SQL2K does not start and returns the following
> error in the agent out file.
> Error 53, Cannot generate SSPI Context
> The agent was working until last week.
> We have researched this error on Microsoft knowldge base and several news
> groups and none of the stuff we read seems to work.
> We CAN connect to the DB using the full DNS name or IP. But when trying
to
> use (local), the error appears. It just seems like SQL/Windows lost how
to
> find or resolve (local).
> How does (local) work? This is happening on two servers in the same
Active
> Directory Win2k server domain. Could it be a domain level policy that is
> goofed?
> Thanks for any help.

Error 53: Cannot generate SSPI Context

SQLserveragent service for SQL2K does not start and returns the following
error in the agent out file.
Error 53, Cannot generate SSPI Context
The agent was working until last week.
We have researched this error on Microsoft knowldge base and several news
groups and none of the stuff we read seems to work.
We CAN connect to the DB using the full DNS name or IP. But when trying to
use (local), the error appears. It just seems like SQL/Windows lost how to
find or resolve (local).
How does (local) work? This is happening on two servers in the same Active
Directory Win2k server domain. Could it be a domain level policy that is
goofed?
Thanks for any help."kenwu" <kenwu@.discussions.microsoft.com> wrote in message
news:61B5A8F9-AB41-4192-80D6-F9B96224DBF4@.microsoft.com...
> SQLserveragent service for SQL2K does not start and returns the following
> error in the agent out file.
> Error 53, Cannot generate SSPI Context
> The agent was working until last week.
> We have researched this error on Microsoft knowldge base and several news
> groups and none of the stuff we read seems to work.
> We CAN connect to the DB using the full DNS name or IP. But when trying
to
> use (local), the error appears. It just seems like SQL/Windows lost how
to
> find or resolve (local).
> How does (local) work? This is happening on two servers in the same
Active
> Directory Win2k server domain. Could it be a domain level policy that is
> goofed?
> Thanks for any help.|||need to try to connect to sqlagent using sa account and password. you can do
that with the
sqlagen manager, change authentication and ener account and password, then
restart sqlserver agent. (there is a known kba for this problem sorry I
cannot find it now)
I will then suggest re-booting the server.
I vaguely remember it being something to do with the sqlagent service
accounts not being able to communicate with the domain controller and there
fore not being authenticated,causing the error.
"kenwu" <kenwu@.discussions.microsoft.com> wrote in message
news:61B5A8F9-AB41-4192-80D6-F9B96224DBF4@.microsoft.com...
> SQLserveragent service for SQL2K does not start and returns the following
> error in the agent out file.
> Error 53, Cannot generate SSPI Context
> The agent was working until last week.
> We have researched this error on Microsoft knowldge base and several news
> groups and none of the stuff we read seems to work.
> We CAN connect to the DB using the full DNS name or IP. But when trying
to
> use (local), the error appears. It just seems like SQL/Windows lost how
to
> find or resolve (local).
> How does (local) work? This is happening on two servers in the same
Active
> Directory Win2k server domain. Could it be a domain level policy that is
> goofed?
> Thanks for any help.|||Hi Kenwu,
Look at this kbs:
http://support.microsoft.com/?id=814401
http://support.microsoft.com/?id=811889
http://dotnetjunkies.com/WebLog/pet...02/25/7897.aspx
Good Luck !
Rodrigo Fernandes
MCSE/MCDBA
"kenwu" wrote:

> SQLserveragent service for SQL2K does not start and returns the following
> error in the agent out file.
> Error 53, Cannot generate SSPI Context
> The agent was working until last week.
> We have researched this error on Microsoft knowldge base and several news
> groups and none of the stuff we read seems to work.
> We CAN connect to the DB using the full DNS name or IP. But when trying t
o
> use (local), the error appears. It just seems like SQL/Windows lost how t
o
> find or resolve (local).
> How does (local) work? This is happening on two servers in the same Activ
e
> Directory Win2k server domain. Could it be a domain level policy that is
> goofed?
> Thanks for any help.|||What changes you did last week?
Regards,
Daniel
"kenwu" <kenwu@.discussions.microsoft.com> wrote in message
news:61B5A8F9-AB41-4192-80D6-F9B96224DBF4@.microsoft.com...
> SQLserveragent service for SQL2K does not start and returns the following
> error in the agent out file.
> Error 53, Cannot generate SSPI Context
> The agent was working until last week.
> We have researched this error on Microsoft knowldge base and several news
> groups and none of the stuff we read seems to work.
> We CAN connect to the DB using the full DNS name or IP. But when trying
to
> use (local), the error appears. It just seems like SQL/Windows lost how
to
> find or resolve (local).
> How does (local) work? This is happening on two servers in the same
Active
> Directory Win2k server domain. Could it be a domain level policy that is
> goofed?
> Thanks for any help.

Error 53: Cannot generate SSPI Context

SQLserveragent service for SQL2K does not start and returns the following
error in the agent out file.
Error 53, Cannot generate SSPI Context
The agent was working until last week.
We have researched this error on Microsoft knowldge base and several news
groups and none of the stuff we read seems to work.
We CAN connect to the DB using the full DNS name or IP. But when trying to
use (local), the error appears. It just seems like SQL/Windows lost how to
find or resolve (local).
How does (local) work? This is happening on two servers in the same Active
Directory Win2k server domain. Could it be a domain level policy that is
goofed?
Thanks for any help."kenwu" <kenwu@.discussions.microsoft.com> wrote in message
news:61B5A8F9-AB41-4192-80D6-F9B96224DBF4@.microsoft.com...
> SQLserveragent service for SQL2K does not start and returns the following
> error in the agent out file.
> Error 53, Cannot generate SSPI Context
> The agent was working until last week.
> We have researched this error on Microsoft knowldge base and several news
> groups and none of the stuff we read seems to work.
> We CAN connect to the DB using the full DNS name or IP. But when trying
to
> use (local), the error appears. It just seems like SQL/Windows lost how
to
> find or resolve (local).
> How does (local) work? This is happening on two servers in the same
Active
> Directory Win2k server domain. Could it be a domain level policy that is
> goofed?
> Thanks for any help.|||need to try to connect to sqlagent using sa account and password. you can do
that with the
sqlagen manager, change authentication and ener account and password, then
restart sqlserver agent. (there is a known kba for this problem sorry I
cannot find it now)
I will then suggest re-booting the server.
I vaguely remember it being something to do with the sqlagent service
accounts not being able to communicate with the domain controller and there
fore not being authenticated,causing the error.
"kenwu" <kenwu@.discussions.microsoft.com> wrote in message
news:61B5A8F9-AB41-4192-80D6-F9B96224DBF4@.microsoft.com...
> SQLserveragent service for SQL2K does not start and returns the following
> error in the agent out file.
> Error 53, Cannot generate SSPI Context
> The agent was working until last week.
> We have researched this error on Microsoft knowldge base and several news
> groups and none of the stuff we read seems to work.
> We CAN connect to the DB using the full DNS name or IP. But when trying
to
> use (local), the error appears. It just seems like SQL/Windows lost how
to
> find or resolve (local).
> How does (local) work? This is happening on two servers in the same
Active
> Directory Win2k server domain. Could it be a domain level policy that is
> goofed?
> Thanks for any help.|||Hi Kenwu,
Look at this kbs:
http://support.microsoft.com/?id=814401
http://support.microsoft.com/?id=811889
http://dotnetjunkies.com/WebLog/petergekko/archive/2004/02/25/7897.aspx
Good Luck !
Rodrigo Fernandes
MCSE/MCDBA
"kenwu" wrote:
> SQLserveragent service for SQL2K does not start and returns the following
> error in the agent out file.
> Error 53, Cannot generate SSPI Context
> The agent was working until last week.
> We have researched this error on Microsoft knowldge base and several news
> groups and none of the stuff we read seems to work.
> We CAN connect to the DB using the full DNS name or IP. But when trying to
> use (local), the error appears. It just seems like SQL/Windows lost how to
> find or resolve (local).
> How does (local) work? This is happening on two servers in the same Active
> Directory Win2k server domain. Could it be a domain level policy that is
> goofed?
> Thanks for any help.|||What changes you did last week?
Regards,
Daniel
"kenwu" <kenwu@.discussions.microsoft.com> wrote in message
news:61B5A8F9-AB41-4192-80D6-F9B96224DBF4@.microsoft.com...
> SQLserveragent service for SQL2K does not start and returns the following
> error in the agent out file.
> Error 53, Cannot generate SSPI Context
> The agent was working until last week.
> We have researched this error on Microsoft knowldge base and several news
> groups and none of the stuff we read seems to work.
> We CAN connect to the DB using the full DNS name or IP. But when trying
to
> use (local), the error appears. It just seems like SQL/Windows lost how
to
> find or resolve (local).
> How does (local) work? This is happening on two servers in the same
Active
> Directory Win2k server domain. Could it be a domain level policy that is
> goofed?
> Thanks for any help.

Error 53, Cannot generate SSPI Context

SQLserveragent service for SQL2K does not start and returns the following er
ror in the agent out file.
Error 53, Cannot generate SSPI Context
We have researched this error on Microsoft and several news groups and none
of the stuff we read seems to work.
We CAN connect to the DB using the full DNS name or IP. But when trying to
use (local), the error appears. It just seems like SQL/Windows lost how to
find or resolve (local).
How does (local) work? This is happening on two servers in the same Active
Directory Win2k server domain. Could it be a domain level policy that is go
ofed?
Thanks for any help.'local' used to mean a local pipe as opposed to a Named Pipe. With 2000 we
use Shared Memory.
Check the servername to make sure it is right. Run sp_helpserver and
review the results
the netbios name should have an id of 0 (zero).
Also check the registry on the server itself to make sure the default
netlib is set to DBNETLIB
There should be no aliases for (local) as well.
Check this key in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Client\ConnectTo
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Error 5: Access denied

Upgraded an NT 4 server to WIN2K Server with SQL Server 2000 already
installed. Now getting MSSQLSERVER service startup errors with access
denied despite having domain admin as logon. What gives?
Check the 'logon as' in Control Panel\Services. Did the password for that
account get changed during the upgrade? Or should it be a new login all
together? In any case, check that.
hth
"Terry Johnson" <tjohnson@.contech.com> wrote in message
news:%23etTiUw1EHA.2316@.TK2MSFTNGP15.phx.gbl...
> Upgraded an NT 4 server to WIN2K Server with SQL Server 2000 already
> installed. Now getting MSSQLSERVER service startup errors with access
> denied despite having domain admin as logon. What gives?
>

Error 5 when creating a subscription

Hi,
After pushing a subscription I get an error:
SQL Server Enterprise Manager could not start the service 'SQLSERVERAGENT' on server '***'.
5 - (Access is denied)
How can I fix that?
Thanks in advance,
MikeSounds like the account on the publisher needs rights on the subscriber...

just a shot in the dark...|||How do I set these rights? I am not sure where I need to look for that information. I have admin rights on both servers.

Error 5 Access Denied

Upgraded an NT 4 server to WIN2K Server with SQL Server 2000 already
installed. Now getting MSSQLSERVER service startup errors with access
denied despite having domain admin as logon. What gives'Check the drive in which the Binn files are installed. Make sure they arent
ReadOnly, also try giving explicit rights ot your account to those folders
and try agian.
Thanks.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Error 5 Access Denied

Upgraded an NT 4 server to WIN2K Server with SQL Server 2000 already
installed. Now getting MSSQLSERVER service startup errors with access
denied despite having domain admin as logon. What gives?
Check the drive in which the Binn files are installed. Make sure they arent
ReadOnly, also try giving explicit rights ot your account to those folders
and try agian.
Thanks.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Error 5 Access Denied

Upgraded an NT 4 server to WIN2K Server with SQL Server 2000 already
installed. Now getting MSSQLSERVER service startup errors with access
denied despite having domain admin as logon. What gives'Check the drive in which the Binn files are installed. Make sure they arent
ReadOnly, also try giving explicit rights ot your account to those folders
and try agian.
Thanks.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Error 4064 after attempting restore backup?!

Hi,

We have a little problem for you:

We have 2 servers both running sqlexpress2005, both have service pack 1 installed, both have the same db (same tables etc.) both have the same users with the same passwords. Everything is identical.

What I want to do is move the db. I am attempting to do this by making a backup of the db and restoring it into the second machine. (I am not sure of a more practical way to do this without stopping the SQL server).

This is the problem, the backup process works fine, without error, but when restoring the copy to the other server ,it loses the dbowner property and all default users when attempting to connect recieve a 4064 error even though all the password for users are there. Very strange.

I tried to restore the copy to to the same server that I made the copy and everything works, this only happens when attempting to restore the copy on another machine.

Do you know if this is a possible bug? I have tried to do further test on 4 different machines now and the same happens.

The objective is to be able to send the data from sqlexpress server to another by making a backup and without the need t stop the sql server. Any suggestion would be appreciated.

Adam

Even though the usernames and passwords are the same the users will have different SIDs (SQL Server's unique identifiers for users). To get a list of the orphaned users run the following on the database you restored:

EXEC sp_change_users_login 'Report'

You can then run the following command for each orphaned user to fix their relationship to the database:

EXEC sp_change_users_login 'Auto_Fix', 'user'

|||

William, can you help me with this:

I have the same scenario as the original posting, and the orpahned user (Webuser) can be fixed with the following:

1 - EXEC sp_change_users_login 'Auto_Fix', 'webuser'

2- I must then create a table, and give webuser rights to it.

Until I do step 2 the user webuser still cannot select from any tables

How can I simplify this and allow webuser to use the db without the second step.

thanks for any help

regards

David

|||

hi David,

step 1 only fixex membership to the database users... being a database user only grants (again) membership to "public" database role, and usually public members do not have lots of permissions on database objects... acutally public members do not have read/write permissions on database tables at all.. you have to manually handle that, or make the desired/required database user member of other privileged database roles, like db_datareader/db_datawriter.. or, even better, define appropriate database roles to have the desired granularity in object's permissions...

regards

|||Hi
Sorry I did not explain adequately.

My user already has rights to all necessary tables and other objects in the db.

I am having to update a remote server with the latest version if a db, I have

no online link to that server the only possible method is manually

overwriting the mdb and ldb files

After I re-attach the updated file to the server, the user cannot select from any of those objects that they formerly had access to.

I run the sp to deal with the orphaned users issue, but then, the user still cannot select etc, even though I can look into all the object permissions and the user is shown there. So they are not an orphan, and they have explicit permissions, but they cannot see or use any of the database.

The only way I have found to resolve it is to create a new object, a table say, grant the user permissions, then hey presto suddenly they can see that, and more to the point they can see and use all the objects they are supposed to see. Then I delete the newly created table object.

The act of creating a new table and giving them rights has fixed the problem. But it cannot be the right way to do it.

regards
David

Error 4064 after attempting restore backup?!

Hi,

We have a little problem for you:

We have 2 servers both running sqlexpress2005, both have service pack 1 installed, both have the same db (same tables etc.) both have the same users with the same passwords. Everything is identical.

What I want to do is move the db. I am attempting to do this by making a backup of the db and restoring it into the second machine. (I am not sure of a more practical way to do this without stopping the SQL server).

This is the problem, the backup process works fine, without error, but when restoring the copy to the other server ,it loses the dbowner property and all default users when attempting to connect recieve a 4064 error even though all the password for users are there. Very strange.

I tried to restore the copy to to the same server that I made the copy and everything works, this only happens when attempting to restore the copy on another machine.

Do you know if this is a possible bug? I have tried to do further test on 4 different machines now and the same happens.

The objective is to be able to send the data from sqlexpress server to another by making a backup and without the need t stop the sql server. Any suggestion would be appreciated.

Adam

Even though the usernames and passwords are the same the users will have different SIDs (SQL Server's unique identifiers for users). To get a list of the orphaned users run the following on the database you restored:

EXEC sp_change_users_login 'Report'

You can then run the following command for each orphaned user to fix their relationship to the database:

EXEC sp_change_users_login 'Auto_Fix', 'user'

|||

William, can you help me with this:

I have the same scenario as the original posting, and the orpahned user (Webuser) can be fixed with the following:

1 - EXEC sp_change_users_login 'Auto_Fix', 'webuser'

2- I must then create a table, and give webuser rights to it.

Until I do step 2 the user webuser still cannot select from any tables

How can I simplify this and allow webuser to use the db without the second step.

thanks for any help

regards

David

|||

hi David,

step 1 only fixex membership to the database users... being a database user only grants (again) membership to "public" database role, and usually public members do not have lots of permissions on database objects... acutally public members do not have read/write permissions on database tables at all.. you have to manually handle that, or make the desired/required database user member of other privileged database roles, like db_datareader/db_datawriter.. or, even better, define appropriate database roles to have the desired granularity in object's permissions...

regards

|||Hi
Sorry I did not explain adequately.

My user already has rights to all necessary tables and other objects in the db.

I am having to update a remote server with the latest version if a db, I have

no online link to that server the only possible method is manually

overwriting the mdb and ldb files

After I re-attach the updated file to the server, the user cannot select from any of those objects that they formerly had access to.

I run the sp to deal with the orphaned users issue, but then, the user still cannot select etc, even though I can look into all the object permissions and the user is shown there. So they are not an orphan, and they have explicit permissions, but they cannot see or use any of the database.

The only way I have found to resolve it is to create a new object, a table say, grant the user permissions, then hey presto suddenly they can see that, and more to the point they can see and use all the objects they are supposed to see. Then I delete the newly created table object.

The act of creating a new table and giving them rights has fixed the problem. But it cannot be the right way to do it.

regards
David

Error 26: When connecting to Local Server with SQL Server 2005 Standard Edition

Hi,

I've been trying to fix this error for two days! and I really need the advice of the experts please!

Computer XP Professional Service Pack2, SQL Server 2005 and Visual Studio 2005 (both Standard Edition)

ConnectionString:

<

connectionStrings>

<add name="MyConnectionDB" connectionString="Data Source=(MyServer\SqlServer2005);Initial Catalog=MyDB;User ID=sa;Password=PasswordforSA"providerName="System.Data.SqlClient" />

</connectionStrings>

C# code

string

myConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionDB"].ConnectionString;

SqlConnection myConnection = new SqlConnection(myConnectionString);

myConnection.Open();

Here I get the error: {"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"}

What I checked so far:

ServerName and Instance NameOK!Database, Username and passwordOK!SQL Server 2005 Surface Are Configuration - Remote connections using both TCP/IP and Pipe NamesOK!SQL Configuration Manager - Protocolos Enabled (TCP/IP, Pipes, shared)OK!SQL Server Browse running and activeOK!

Am I missing something? I never had this problem with SQL Server 2000 and Visual Studio 2003.

Thanks in advance for any help or suggestion you can give me.

Sasa

Nevermind, I reinstalled again .NET 2.0 and it works. I can't believe that I expend two days thinking it was a configuration setting

Error 26: When connecting to Local Server with SQL Server 2005 Standard Edition

Hi,

I've been trying to fix this error for two days! and I really need the advice of the experts please!

Computer XP Professional Service Pack2, SQL Server 2005 and Visual Studio 2005 (both Standard Edition)

ConnectionString:

<connectionStrings>

<add name="MyConnectionDB" connectionString="Data Source=(MyServer\SqlServer2005);Initial Catalog=MyDB;User ID=sa;Password=PasswordforSA" providerName="System.Data.SqlClient" />

</connectionStrings>

C# code

string

myConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionDB"].ConnectionString;

SqlConnection myConnection = new SqlConnection(myConnectionString);

myConnection.Open();

Here I get the error: {"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"}

What I checked so far:

ServerName and Instance Name OK! Database, Username and password OK! SQL Server 2005 Surface Are Configuration - Remote connections using both TCP/IP and Pipe Names OK! SQL Configuration Manager - Protocolos Enabled (TCP/IP, Pipes, shared) OK! SQL Server Browse running and active OK! No Firewall

Am I missing something? I never had this problem with SQL Server 2000 and Visual Studio 2003.

Thanks in advance for any help or suggestion you can give me.

Sasa

Hi there,

I have managed to replicate your problem on my machine. I also managed to resolve it by removing the brackets, i.e. the "(" and ")", from the name of your data source.

So, your connection string would be:

<add name="MyConnectionDB" connectionString="Data Source=MyServer\SqlServer2005;Initial Catalog=MyDB;User ID=sa;Password=PasswordforSA" providerName="System.Data.SqlClient" />

Before removing the brackets I got the same errors you were getting. After removing the brackets I could connect successfully.

Have a go at removing the brackets and see if that works for you.

Hope this helps, but sorry if it doesn't
|||

You are my hero!!!

I don't know how many times I checked that connectionstring, I was thinking it was any configuration setting.

Thanks a lot

|||Where do I go to remove the "()"?|||

you need to open de Web.config file in the <connectionStrings> section

Sasa

|||

Hi

I thought it is a joke to remove the "()" but it works, You are my hero.

barak

Error 26: When connecting to Local Server with SQL Server 2005 Standard Edition

Hi,

I've been trying to fix this error for two days! and I really need the advice of the experts please!

Computer XP Professional Service Pack2, SQL Server 2005 and Visual Studio 2005 (both Standard Edition)

ConnectionString:

<connectionStrings>

<add name="MyConnectionDB" connectionString="Data Source=(MyServer\SqlServer2005);Initial Catalog=MyDB;User ID=sa;Password=PasswordforSA" providerName="System.Data.SqlClient" />

</connectionStrings>

C# code

string

myConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionDB"].ConnectionString;

SqlConnection myConnection = new SqlConnection(myConnectionString);

myConnection.Open();

Here I get the error: {"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"}

What I checked so far:

ServerName and Instance Name OK! Database, Username and password OK! SQL Server 2005 Surface Are Configuration - Remote connections using both TCP/IP and Pipe Names OK! SQL Configuration Manager - Protocolos Enabled (TCP/IP, Pipes, shared) OK! SQL Server Browse running and active OK! No Firewall

Am I missing something? I never had this problem with SQL Server 2000 and Visual Studio 2003.

Thanks in advance for any help or suggestion you can give me.

Sasa

Hi there,

I have managed to replicate your problem on my machine. I also managed to resolve it by removing the brackets, i.e. the "(" and ")", from the name of your data source.

So, your connection string would be:

<add name="MyConnectionDB" connectionString="Data Source=MyServer\SqlServer2005;Initial Catalog=MyDB;User ID=sa;Password=PasswordforSA" providerName="System.Data.SqlClient" />

Before removing the brackets I got the same errors you were getting. After removing the brackets I could connect successfully.

Have a go at removing the brackets and see if that works for you.

Hope this helps, but sorry if it doesn't
|||

You are my hero!!!

I don't know how many times I checked that connectionstring, I was thinking it was any configuration setting.

Thanks a lot

|||Where do I go to remove the "()"?|||

you need to open de Web.config file in the <connectionStrings> section

Sasa

|||

Hi

I thought it is a joke to remove the "()" but it works, You are my hero.

barak

Error 26: When connecting to Local Server with SQL Server 2005 Standard Edition

Hi,

I've been trying to fix this error for two days! and I really need the advice of the experts please!

Computer XP Professional Service Pack2, SQL Server 2005 and Visual Studio 2005 (both Standard Edition)

ConnectionString:

<connectionStrings>

<add name="MyConnectionDB" connectionString="Data Source=(MyServer\SqlServer2005);Initial Catalog=MyDB;User ID=sa;Password=PasswordforSA" providerName="System.Data.SqlClient" />

</connectionStrings>

C# code

string

myConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionDB"].ConnectionString;

SqlConnection myConnection = new SqlConnection(myConnectionString);

myConnection.Open();

Here I get the error: {"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"}

What I checked so far:

ServerName and Instance Name OK! Database, Username and password OK! SQL Server 2005 Surface Are Configuration - Remote connections using both TCP/IP and Pipe Names OK! SQL Configuration Manager - Protocolos Enabled (TCP/IP, Pipes, shared) OK! SQL Server Browse running and active OK! No Firewall

Am I missing something? I never had this problem with SQL Server 2000 and Visual Studio 2003.

Thanks in advance for any help or suggestion you can give me.

Sasa

Hi there,

I have managed to replicate your problem on my machine. I also managed to resolve it by removing the brackets, i.e. the "(" and ")", from the name of your data source.

So, your connection string would be:

<add name="MyConnectionDB" connectionString="Data Source=MyServer\SqlServer2005;Initial Catalog=MyDB;User ID=sa;Password=PasswordforSA" providerName="System.Data.SqlClient" />

Before removing the brackets I got the same errors you were getting. After removing the brackets I could connect successfully.

Have a go at removing the brackets and see if that works for you.

Hope this helps, but sorry if it doesn't
|||

You are my hero!!!

I don't know how many times I checked that connectionstring, I was thinking it was any configuration setting.

Thanks a lot

|||Where do I go to remove the "()"?|||

you need to open de Web.config file in the <connectionStrings> section

Sasa

|||

Hi

I thought it is a joke to remove the "()" but it works, You are my hero.

barak

Error 22042: xp_setsqlsecurity() raises error -2147024893

Hello MS-Support,
Please help with the following problem:
I want to change the service account under which sqlserver runs to another
local user with less privileges.
My problem is that the following error raises in Enterprise manager:
Error 22042: xp_setsqlsecurity() raises error -2147024893 'The system cannot
find the path specified'
regards
Why does MS not reply to my problem?
on the homepage there is the information that a support person will reply
within 2 business days!
regards
"madrianr" wrote:

> Hello MS-Support,
> Please help with the following problem:
> I want to change the service account under which sqlserver runs to another
> local user with less privileges.
> My problem is that the following error raises in Enterprise manager:
> Error 22042: xp_setsqlsecurity() raises error -2147024893 'The system cannot
> find the path specified'
> regards
|||Try this; it should help.
How to change the SQL Server or SQL Server Agent Service account without
using SQL Enterprise Manager in SQL Server 2000
http://support.microsoft.com/default...b;en-us;283811
Sincerely,
Anthony Thomas

"madrianr" <madrianr@.discussions.microsoft.com> wrote in message
news:E31EB027-8D19-4F08-BA15-B97315C5BDD5@.microsoft.com...
Why does MS not reply to my problem?
on the homepage there is the information that a support person will reply
within 2 business days!
regards
"madrianr" wrote:

> Hello MS-Support,
> Please help with the following problem:
> I want to change the service account under which sqlserver runs to another
> local user with less privileges.
> My problem is that the following error raises in Enterprise manager:
> Error 22042: xp_setsqlsecurity() raises error -2147024893 'The system
cannot
> find the path specified'
> regards
|||After contacting MS-Support I have the solution for that problem:
......
After looking into the registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Setup
I found out that the SQLDATAROOT is "D:\Datenbank\mssql instead of
"D:\Datenbank".
I changed this value and now it is possible to switch the account with the
Enterprise Manager.
After that I have to give that account full access to the folder
"c:\protokoll" (where the logs are present)
to start the sqlserver service/agent.
Now it seems that all works ok!
"Anthony Thomas" <ALThomas@.kc.rr.com> schrieb im Newsbeitrag
news:OOzJO7kbFHA.2288@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Try this; it should help.
> How to change the SQL Server or SQL Server Agent Service account without
> using SQL Enterprise Manager in SQL Server 2000
> http://support.microsoft.com/default...b;en-us;283811
> Sincerely,
>
> Anthony Thomas
>
> --
> "madrianr" <madrianr@.discussions.microsoft.com> wrote in message
> news:E31EB027-8D19-4F08-BA15-B97315C5BDD5@.microsoft.com...
> Why does MS not reply to my problem?
> on the homepage there is the information that a support person will reply
> within 2 business days!
> regards
> "madrianr" wrote:
another
> cannot
>

Error 22042: xp_setsqlsecurity() raises error -2147024893

Hello MS-Support,
Please help with the following problem:
I want to change the service account under which sqlserver runs to another
local user with less privileges.
My problem is that the following error raises in Enterprise manager:
Error 22042: xp_setsqlsecurity() raises error -2147024893 'The system cannot
find the path specified'
regardsWhy does MS not reply to my problem?
on the homepage there is the information that a support person will reply
within 2 business days!
regards
"madrianr" wrote:

> Hello MS-Support,
> Please help with the following problem:
> I want to change the service account under which sqlserver runs to another
> local user with less privileges.
> My problem is that the following error raises in Enterprise manager:
> Error 22042: xp_setsqlsecurity() raises error -2147024893 'The system cann
ot
> find the path specified'
> regards|||Try this; it should help.
How to change the SQL Server or SQL Server Agent Service account without
using SQL Enterprise Manager in SQL Server 2000
http://support.microsoft.com/defaul...kb;en-us;283811
Sincerely,
Anthony Thomas
"madrianr" <madrianr@.discussions.microsoft.com> wrote in message
news:E31EB027-8D19-4F08-BA15-B97315C5BDD5@.microsoft.com...
Why does MS not reply to my problem?
on the homepage there is the information that a support person will reply
within 2 business days!
regards
"madrianr" wrote:

> Hello MS-Support,
> Please help with the following problem:
> I want to change the service account under which sqlserver runs to another
> local user with less privileges.
> My problem is that the following error raises in Enterprise manager:
> Error 22042: xp_setsqlsecurity() raises error -2147024893 'The system
cannot
> find the path specified'
> regards|||After contacting MS-Support I have the solution for that problem:
......
After looking into the registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Setup
I found out that the SQLDATAROOT is "D:\Datenbank\mssql instead of
"D:\Datenbank".
I changed this value and now it is possible to switch the account with the
Enterprise Manager.
After that I have to give that account full access to the folder
"c:\protokoll" (where the logs are present)
to start the sqlserver service/agent.
Now it seems that all works ok!
"Anthony Thomas" <ALThomas@.kc.rr.com> schrieb im Newsbeitrag
news:OOzJO7kbFHA.2288@.TK2MSFTNGP14.phx.gbl...
> Try this; it should help.
> How to change the SQL Server or SQL Server Agent Service account without
> using SQL Enterprise Manager in SQL Server 2000
> http://support.microsoft.com/defaul...kb;en-us;283811
> Sincerely,
>
> Anthony Thomas
>
> --
> "madrianr" <madrianr@.discussions.microsoft.com> wrote in message
> news:E31EB027-8D19-4F08-BA15-B97315C5BDD5@.microsoft.com...
> Why does MS not reply to my problem?
> on the homepage there is the information that a support person will reply
> within 2 business days!
> regards
> "madrianr" wrote:
>
another[vbcol=seagreen]
> cannot
>

Error 22042: xp_setsqlsecurity() raises error -2147024893

Hello MS-Support,
Please help with the following problem:
I want to change the service account under which sqlserver runs to another
local user with less privileges.
My problem is that the following error raises in Enterprise manager:
Error 22042: xp_setsqlsecurity() raises error -2147024893 'The system cannot
find the path specified'
regardsWhy does MS not reply to my problem?
on the homepage there is the information that a support person will reply
within 2 business days!
regards
"madrianr" wrote:
> Hello MS-Support,
> Please help with the following problem:
> I want to change the service account under which sqlserver runs to another
> local user with less privileges.
> My problem is that the following error raises in Enterprise manager:
> Error 22042: xp_setsqlsecurity() raises error -2147024893 'The system cannot
> find the path specified'
> regards|||Try this; it should help.
How to change the SQL Server or SQL Server Agent Service account without
using SQL Enterprise Manager in SQL Server 2000
http://support.microsoft.com/default.aspx?scid=kb;en-us;283811
Sincerely,
Anthony Thomas
"madrianr" <madrianr@.discussions.microsoft.com> wrote in message
news:E31EB027-8D19-4F08-BA15-B97315C5BDD5@.microsoft.com...
Why does MS not reply to my problem?
on the homepage there is the information that a support person will reply
within 2 business days!
regards
"madrianr" wrote:
> Hello MS-Support,
> Please help with the following problem:
> I want to change the service account under which sqlserver runs to another
> local user with less privileges.
> My problem is that the following error raises in Enterprise manager:
> Error 22042: xp_setsqlsecurity() raises error -2147024893 'The system
cannot
> find the path specified'
> regards|||After contacting MS-Support I have the solution for that problem:
......
After looking into the registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
I found out that the SQLDATAROOT is "D:\Datenbank\mssql instead of
"D:\Datenbank".
I changed this value and now it is possible to switch the account with the
Enterprise Manager.
After that I have to give that account full access to the folder
"c:\protokoll" (where the logs are present)
to start the sqlserver service/agent.
Now it seems that all works ok!
"Anthony Thomas" <ALThomas@.kc.rr.com> schrieb im Newsbeitrag
news:OOzJO7kbFHA.2288@.TK2MSFTNGP14.phx.gbl...
> Try this; it should help.
> How to change the SQL Server or SQL Server Agent Service account without
> using SQL Enterprise Manager in SQL Server 2000
> http://support.microsoft.com/default.aspx?scid=kb;en-us;283811
> Sincerely,
>
> Anthony Thomas
>
> --
> "madrianr" <madrianr@.discussions.microsoft.com> wrote in message
> news:E31EB027-8D19-4F08-BA15-B97315C5BDD5@.microsoft.com...
> Why does MS not reply to my problem?
> on the homepage there is the information that a support person will reply
> within 2 business days!
> regards
> "madrianr" wrote:
> > Hello MS-Support,
> >
> > Please help with the following problem:
> >
> > I want to change the service account under which sqlserver runs to
another
> > local user with less privileges.
> >
> > My problem is that the following error raises in Enterprise manager:
> >
> > Error 22042: xp_setsqlsecurity() raises error -2147024893 'The system
> cannot
> > find the path specified'
> >
> > regards
>