Can't see mapped drives

Hi,
Am unable to see mapped drive when going to restore db in
sql 2000 enterprise manager. Service is set to start
with a lan acct. acct is in the local machine admin grp
and remote machine admin grp.
Any suggestions:-)
Thanks in advanceDo not use mapped drives for backup\restore with SQL server. Use UNC names
instead. SQL may or may not see a mapped drive correctly but will always
find a UNC share that it has permission and connection to.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:8f9b01c40541$3ade3230$a301280a@.phx.gbl...
> Hi,
> Am unable to see mapped drive when going to restore db in
> sql 2000 enterprise manager. Service is set to start
> with a lan acct. acct is in the local machine admin grp
> and remote machine admin grp.
> Any suggestions:-)
> Thanks in advance|||Hi,
You cant see the mapped drives in Enterprise manager. Rather you can
directly mention the UNC path directly in Enterprise manager or use
Restore database command from Query Analyzer.
Restore database <dbname> from disk='\\machinename\share\backupfile.bak'
with stats=10 With
Move 'logicaldatafile' to physcaldatafile',
Move 'logiallogfile' to physcallogfile'
stats will show the restore status
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:8f9b01c40541$3ade3230$a301280a@.phx.gbl...
> Hi,
> Am unable to see mapped drive when going to restore db in
> sql 2000 enterprise manager. Service is set to start
> with a lan acct. acct is in the local machine admin grp
> and remote machine admin grp.
> Any suggestions:-)
> Thanks in advance|||It is possible to get SQL Server to use mapped network drives, through the u
se of trace flag 1807.
Check out knowledge base article #304261 for full details
http://support.microsoft.com/defaul...1&Product=sql2k

Cant get windows authentication in Mixed Mode to work .... any ideas?

Hey all,
I've got SQL 2K running in mixed mode. I created a windows acct (mytest) in the administrator group, and then created a corresponding SQL account (mytest) using the windows authentication. In the EM it comes up as a "Windows User" type of acct. (The account is not a domain account but a workstation one).

I've configured my app to use that account for access to the database but I get the following error message when I send the connection request to the datasource:

Connection Failed:
SQL State:28000
SQL Server Error: 18456
Login failed for user myworkstation\mytest

I can connect just fine using the sql accounts, but it doesn't want to play nice when I need the windows authentication portion to work.
What exactly am I doing wrong?
Any input is greatly appreciated !
Regards,
TammyA couple of things come to mind...

1. If mytest is a member of the workstation Administrators group, then you do not (necessarily) need a separate SQL account for it. The BUILTIN\Administrators account permits all members of the local Administrators group to have access (in the default config anyway).

2. If you have denied access to the BUILTIN\Administrators group, then myWorkstation\mytest will not have access (Deny trumps permit).

3. Just a head check: you said that myworkstation\mytest was NOT a domain account (when you set it up). You didn't by chance login under the domain?

Just my $ .02. It's not much, I'll grant you, but it was what I could come up with.

hmscott|||Mr. Scott !
I'm such an idiot ....
I had forgotten that I denied access to the builtin Administrator account. I turned it back on and VOILA !
Mr. Scott - 1
Tammy - 0
Gracias !

Originally posted by hmscott
A couple of things come to mind...

1. If mytest is a member of the workstation Administrators group, then you do not (necessarily) need a separate SQL account for it. The BUILTIN\Administrators account permits all members of the local Administrators group to have access (in the default config anyway).

2. If you have denied access to the BUILTIN\Administrators group, then myWorkstation\mytest will not have access (Deny trumps permit).

3. Just a head check: you said that myworkstation\mytest was NOT a domain account (when you set it up). You didn't by chance login under the domain?

Just my $ .02. It's not much, I'll grant you, but it was what I could come up with.

hmscott

Can't get SQL 2000 agent to run

Need urgent help. The sql 2000 agent service never runs. Tried to run under a
domain admin acct which belongs to SysAdmins role and still doesn't run. Ran
the sqlagent with -c -v switches from command line and got foll. 2 errors:
[298] sql server error 170, line 47: incorrect syntax near 'collate'.
[sqlstate 42000] (connisloginsysadmin)
[000] SQLServerAgent must be able to connect to SQLServer as SysAdmin,
but '(Unknown)' is not a member of the SysAdmin role
Tried the microsoft kb solns and still not running. Need this resolved
urgently. Pls help. Thks. We use sql 2000, sp4.
Hi
How about using Service Control Manager.
EXEC master..xp_servicecontrol 'QUERYSTATE', 'SQLSERVERAGENT'
"duchov" <duchov@.discussions.microsoft.com> wrote in message
news:588DF7E4-D96A-4BAD-A1A5-44636138EE54@.microsoft.com...
> Need urgent help. The sql 2000 agent service never runs. Tried to run
> under a
> domain admin acct which belongs to SysAdmins role and still doesn't run.
> Ran
> the sqlagent with -c -v switches from command line and got foll. 2 errors:
> [298] sql server error 170, line 47: incorrect syntax near 'collate'.
> [sqlstate 42000] (connisloginsysadmin)
> [000] SQLServerAgent must be able to connect to SQLServer as SysAdmin,
> but '(Unknown)' is not a member of the SysAdmin role
> Tried the microsoft kb solns and still not running. Need this resolved
> urgently. Pls help. Thks. We use sql 2000, sp4.
|||Hi
I don't see collate in any of the statements issued when SQL Agent is
stopped or started. You may want to profile the agent starting (including
errors and exception events) to see what is being processed.
Is there any other messages in the SQL Server logs?
Have you tried running this as the system account?
John
John
"duchov" wrote:

> Need urgent help. The sql 2000 agent service never runs. Tried to run under a
> domain admin acct which belongs to SysAdmins role and still doesn't run. Ran
> the sqlagent with -c -v switches from command line and got foll. 2 errors:
> [298] sql server error 170, line 47: incorrect syntax near 'collate'.
> [sqlstate 42000] (connisloginsysadmin)
> [000] SQLServerAgent must be able to connect to SQLServer as SysAdmin,
> but '(Unknown)' is not a member of the SysAdmin role
> Tried the microsoft kb solns and still not running. Need this resolved
> urgently. Pls help. Thks. We use sql 2000, sp4.
|||"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I don't see collate in any of the statements issued when SQL Agent is
> stopped or started. You may want to profile the agent starting (including
> errors and exception events) to see what is being processed.
> Is there any other messages in the SQL Server logs?
> Have you tried running this as the system account?
> John
> John
> "duchov" wrote:
|||Hi John/Uri
setting the agent acct from either EM or service ctrl mngr has same result.
Only other errors in the sql logs are:
1) Operating system error = 31(A device attached to the system is not
functioning.).
This error is in multiple logs though not the current log. Any ideas on this?
2) spid52 Error: 15457, Severity: 0, State: 1 (a sql support site said You
will see that 15457 error
popup if you pull up your SQL Server properties.)
3) Configuration option 'show advanced options' changed from 1 to 1. Run the
RECONFIGURE statement
to install..
Errors 2 & 3 are in today's log only and seems to be b/c checked the sql
server properties
Initially agent was under local sys acct n it never ran which is y tried a
domain admin acct but still no success
How do I profile the agent startup/shutdown? I ran a trace via sql profiler
(included errors/exceptions) and manually tried to start agent. the trace
showed 2 records under an application name of sqlAgent - initial boot probe
with no info really. is this what u meant?
Reason this is important is b/c I scheduled some nightly sql backups to disk
n they never run b/c the agent service never starts. Hope u can help.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I don't see collate in any of the statements issued when SQL Agent is
> stopped or started. You may want to profile the agent starting (including
> errors and exception events) to see what is being processed.
> Is there any other messages in the SQL Server logs?
> Have you tried running this as the system account?
> John
> John
> "duchov" wrote:
|||Hi
An Operating System Errors seems like the problem is external to SQL server,
is there anything in the System Event logs?
John
"duchov" <duchov@.discussions.microsoft.com> wrote in message
news:885311AD-1FAC-4745-9430-D1F1A573796E@.microsoft.com...[vbcol=seagreen]
> Hi John/Uri
> setting the agent acct from either EM or service ctrl mngr has same
> result.
> Only other errors in the sql logs are:
> 1) Operating system error = 31(A device attached to the system is not
> functioning.).
> This error is in multiple logs though not the current log. Any ideas on
> this?
> 2) spid52 Error: 15457, Severity: 0, State: 1 (a sql support site said You
> will see that 15457 error
> popup if you pull up your SQL Server properties.)
> 3) Configuration option 'show advanced options' changed from 1 to 1. Run
> the
> RECONFIGURE statement
> to install..
> Errors 2 & 3 are in today's log only and seems to be b/c checked the sql
> server properties
> Initially agent was under local sys acct n it never ran which is y tried a
> domain admin acct but still no success
> How do I profile the agent startup/shutdown? I ran a trace via sql
> profiler
> (included errors/exceptions) and manually tried to start agent. the trace
> showed 2 records under an application name of sqlAgent - initial boot
> probe
> with no info really. is this what u meant?
> Reason this is important is b/c I scheduled some nightly sql backups to
> disk
> n they never run b/c the agent service never starts. Hope u can help.
> "John Bell" wrote:

Can't get SQL 2000 agent to run

Need urgent help. The sql 2000 agent service never runs. Tried to run under
a
domain admin acct which belongs to SysAdmins role and still doesn't run. Ran
the sqlagent with -c -v switches from command line and got foll. 2 errors:
[298] sql server error 170, line 47: incorrect syntax near 'collate'.
[sqlstate 42000] (connisloginsysadmin)
[000] SQLServerAgent must be able to connect to SQLServer as SysAdmin,
but '(Unknown)' is not a member of the SysAdmin role
Tried the microsoft kb solns and still not running. Need this resolved
urgently. Pls help. Thks. We use sql 2000, sp4.Hi
How about using Service Control Manager.
EXEC master..xp_servicecontrol 'QUERYSTATE', 'SQLSERVERAGENT'
"duchov" <duchov@.discussions.microsoft.com> wrote in message
news:588DF7E4-D96A-4BAD-A1A5-44636138EE54@.microsoft.com...
> Need urgent help. The sql 2000 agent service never runs. Tried to run
> under a
> domain admin acct which belongs to SysAdmins role and still doesn't run.
> Ran
> the sqlagent with -c -v switches from command line and got foll. 2 errors:
> [298] sql server error 170, line 47: incorrect syntax near 'collate'.
> [sqlstate 42000] (connisloginsysadmin)
> [000] SQLServerAgent must be able to connect to SQLServer as SysAdmin,
> but '(Unknown)' is not a member of the SysAdmin role
> Tried the microsoft kb solns and still not running. Need this resolved
> urgently. Pls help. Thks. We use sql 2000, sp4.|||Hi
I don't see collate in any of the statements issued when SQL Agent is
stopped or started. You may want to profile the agent starting (including
errors and exception events) to see what is being processed.
Is there any other messages in the SQL Server logs?
Have you tried running this as the system account?
John
John
"duchov" wrote:

> Need urgent help. The sql 2000 agent service never runs. Tried to run unde
r a
> domain admin acct which belongs to SysAdmins role and still doesn't run. R
an
> the sqlagent with -c -v switches from command line and got foll. 2 errors:
> [298] sql server error 170, line 47: incorrect syntax near 'collate'.
> [sqlstate 42000] (connisloginsysadmin)
> [000] SQLServerAgent must be able to connect to SQLServer as SysAdmin,
> but '(Unknown)' is not a member of the SysAdmin role
> Tried the microsoft kb solns and still not running. Need this resolved
> urgently. Pls help. Thks. We use sql 2000, sp4.|||"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I don't see collate in any of the statements issued when SQL Agent is
> stopped or started. You may want to profile the agent starting (including
> errors and exception events) to see what is being processed.
> Is there any other messages in the SQL Server logs?
> Have you tried running this as the system account?
> John
> John
> "duchov" wrote:
>|||Hi John/Uri
setting the agent acct from either EM or service ctrl mngr has same result.
Only other errors in the sql logs are:
1) Operating system error = 31(A device attached to the system is not
functioning.).
This error is in multiple logs though not the current log. Any ideas on this
?
2) spid52 Error: 15457, Severity: 0, State: 1 (a sql support site said You
will see that 15457 error
popup if you pull up your SQL Server properties.)
3) Configuration option 'show advanced options' changed from 1 to 1. Run the
RECONFIGURE statement
to install..
Errors 2 & 3 are in today's log only and seems to be b/c checked the sql
server properties
Initially agent was under local sys acct n it never ran which is y tried a
domain admin acct but still no success
How do I profile the agent startup/shutdown? I ran a trace via sql profiler
(included errors/exceptions) and manually tried to start agent. the trace
showed 2 records under an application name of sqlAgent - initial boot probe
with no info really. is this what u meant?
Reason this is important is b/c I scheduled some nightly sql backups to disk
n they never run b/c the agent service never starts. Hope u can help.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I don't see collate in any of the statements issued when SQL Agent is
> stopped or started. You may want to profile the agent starting (including
> errors and exception events) to see what is being processed.
> Is there any other messages in the SQL Server logs?
> Have you tried running this as the system account?
> John
> John
> "duchov" wrote:
>|||Hi
An Operating System Errors seems like the problem is external to SQL server,
is there anything in the System Event logs?
John
"duchov" <duchov@.discussions.microsoft.com> wrote in message
news:885311AD-1FAC-4745-9430-D1F1A573796E@.microsoft.com...[vbcol=seagreen]
> Hi John/Uri
> setting the agent acct from either EM or service ctrl mngr has same
> result.
> Only other errors in the sql logs are:
> 1) Operating system error = 31(A device attached to the system is not
> functioning.).
> This error is in multiple logs though not the current log. Any ideas on
> this?
> 2) spid52 Error: 15457, Severity: 0, State: 1 (a sql support site said You
> will see that 15457 error
> popup if you pull up your SQL Server properties.)
> 3) Configuration option 'show advanced options' changed from 1 to 1. Run
> the
> RECONFIGURE statement
> to install..
> Errors 2 & 3 are in today's log only and seems to be b/c checked the sql
> server properties
> Initially agent was under local sys acct n it never ran which is y tried a
> domain admin acct but still no success
> How do I profile the agent startup/shutdown? I ran a trace via sql
> profiler
> (included errors/exceptions) and manually tried to start agent. the trace
> showed 2 records under an application name of sqlAgent - initial boot
> probe
> with no info really. is this what u meant?
> Reason this is important is b/c I scheduled some nightly sql backups to
> disk
> n they never run b/c the agent service never starts. Hope u can help.
> "John Bell" wrote:
>

Can't get SQL 2000 agent to run

Need urgent help. The sql 2000 agent service never runs. Tried to run under a
domain admin acct which belongs to SysAdmins role and still doesn't run. Ran
the sqlagent with -c -v switches from command line and got foll. 2 errors:
[298] sql server error 170, line 47: incorrect syntax near 'collate'.
[sqlstate 42000] (connisloginsysadmin)
[000] SQLServerAgent must be able to connect to SQLServer as SysAdmin,
but '(Unknown)' is not a member of the SysAdmin role
Tried the microsoft kb solns and still not running. Need this resolved
urgently. Pls help. Thks. We use sql 2000, sp4.Hi
How about using Service Control Manager.
EXEC master..xp_servicecontrol 'QUERYSTATE', 'SQLSERVERAGENT'
"duchov" <duchov@.discussions.microsoft.com> wrote in message
news:588DF7E4-D96A-4BAD-A1A5-44636138EE54@.microsoft.com...
> Need urgent help. The sql 2000 agent service never runs. Tried to run
> under a
> domain admin acct which belongs to SysAdmins role and still doesn't run.
> Ran
> the sqlagent with -c -v switches from command line and got foll. 2 errors:
> [298] sql server error 170, line 47: incorrect syntax near 'collate'.
> [sqlstate 42000] (connisloginsysadmin)
> [000] SQLServerAgent must be able to connect to SQLServer as SysAdmin,
> but '(Unknown)' is not a member of the SysAdmin role
> Tried the microsoft kb solns and still not running. Need this resolved
> urgently. Pls help. Thks. We use sql 2000, sp4.|||Hi
I don't see collate in any of the statements issued when SQL Agent is
stopped or started. You may want to profile the agent starting (including
errors and exception events) to see what is being processed.
Is there any other messages in the SQL Server logs?
Have you tried running this as the system account?
John
John
"duchov" wrote:
> Need urgent help. The sql 2000 agent service never runs. Tried to run under a
> domain admin acct which belongs to SysAdmins role and still doesn't run. Ran
> the sqlagent with -c -v switches from command line and got foll. 2 errors:
> [298] sql server error 170, line 47: incorrect syntax near 'collate'.
> [sqlstate 42000] (connisloginsysadmin)
> [000] SQLServerAgent must be able to connect to SQLServer as SysAdmin,
> but '(Unknown)' is not a member of the SysAdmin role
> Tried the microsoft kb solns and still not running. Need this resolved
> urgently. Pls help. Thks. We use sql 2000, sp4.|||"John Bell" wrote:
> Hi
> I don't see collate in any of the statements issued when SQL Agent is
> stopped or started. You may want to profile the agent starting (including
> errors and exception events) to see what is being processed.
> Is there any other messages in the SQL Server logs?
> Have you tried running this as the system account?
> John
> John
> "duchov" wrote:
> > Need urgent help. The sql 2000 agent service never runs. Tried to run under a
> > domain admin acct which belongs to SysAdmins role and still doesn't run. Ran
> > the sqlagent with -c -v switches from command line and got foll. 2 errors:
> >
> > [298] sql server error 170, line 47: incorrect syntax near 'collate'.
> >
> > [sqlstate 42000] (connisloginsysadmin)
> >
> > [000] SQLServerAgent must be able to connect to SQLServer as SysAdmin,
> >
> > but '(Unknown)' is not a member of the SysAdmin role
> > Tried the microsoft kb solns and still not running. Need this resolved
> > urgently. Pls help. Thks. We use sql 2000, sp4.|||Hi John/Uri
setting the agent acct from either EM or service ctrl mngr has same result.
Only other errors in the sql logs are:
1) Operating system error = 31(A device attached to the system is not
functioning.).
This error is in multiple logs though not the current log. Any ideas on this?
2) spid52 Error: 15457, Severity: 0, State: 1 (a sql support site said You
will see that 15457 error
popup if you pull up your SQL Server properties.)
3) Configuration option 'show advanced options' changed from 1 to 1. Run the
RECONFIGURE statement
to install..
Errors 2 & 3 are in today's log only and seems to be b/c checked the sql
server properties
Initially agent was under local sys acct n it never ran which is y tried a
domain admin acct but still no success
How do I profile the agent startup/shutdown? I ran a trace via sql profiler
(included errors/exceptions) and manually tried to start agent. the trace
showed 2 records under an application name of sqlAgent - initial boot probe
with no info really. is this what u meant?
Reason this is important is b/c I scheduled some nightly sql backups to disk
n they never run b/c the agent service never starts. Hope u can help.
"John Bell" wrote:
> Hi
> I don't see collate in any of the statements issued when SQL Agent is
> stopped or started. You may want to profile the agent starting (including
> errors and exception events) to see what is being processed.
> Is there any other messages in the SQL Server logs?
> Have you tried running this as the system account?
> John
> John
> "duchov" wrote:
> > Need urgent help. The sql 2000 agent service never runs. Tried to run under a
> > domain admin acct which belongs to SysAdmins role and still doesn't run. Ran
> > the sqlagent with -c -v switches from command line and got foll. 2 errors:
> >
> > [298] sql server error 170, line 47: incorrect syntax near 'collate'.
> >
> > [sqlstate 42000] (connisloginsysadmin)
> >
> > [000] SQLServerAgent must be able to connect to SQLServer as SysAdmin,
> >
> > but '(Unknown)' is not a member of the SysAdmin role
> > Tried the microsoft kb solns and still not running. Need this resolved
> > urgently. Pls help. Thks. We use sql 2000, sp4.|||Hi
An Operating System Errors seems like the problem is external to SQL server,
is there anything in the System Event logs?
John
"duchov" <duchov@.discussions.microsoft.com> wrote in message
news:885311AD-1FAC-4745-9430-D1F1A573796E@.microsoft.com...
> Hi John/Uri
> setting the agent acct from either EM or service ctrl mngr has same
> result.
> Only other errors in the sql logs are:
> 1) Operating system error = 31(A device attached to the system is not
> functioning.).
> This error is in multiple logs though not the current log. Any ideas on
> this?
> 2) spid52 Error: 15457, Severity: 0, State: 1 (a sql support site said You
> will see that 15457 error
> popup if you pull up your SQL Server properties.)
> 3) Configuration option 'show advanced options' changed from 1 to 1. Run
> the
> RECONFIGURE statement
> to install..
> Errors 2 & 3 are in today's log only and seems to be b/c checked the sql
> server properties
> Initially agent was under local sys acct n it never ran which is y tried a
> domain admin acct but still no success
> How do I profile the agent startup/shutdown? I ran a trace via sql
> profiler
> (included errors/exceptions) and manually tried to start agent. the trace
> showed 2 records under an application name of sqlAgent - initial boot
> probe
> with no info really. is this what u meant?
> Reason this is important is b/c I scheduled some nightly sql backups to
> disk
> n they never run b/c the agent service never starts. Hope u can help.
> "John Bell" wrote:
>> Hi
>> I don't see collate in any of the statements issued when SQL Agent is
>> stopped or started. You may want to profile the agent starting (including
>> errors and exception events) to see what is being processed.
>> Is there any other messages in the SQL Server logs?
>> Have you tried running this as the system account?
>> John
>> John
>> "duchov" wrote:
>> > Need urgent help. The sql 2000 agent service never runs. Tried to run
>> > under a
>> > domain admin acct which belongs to SysAdmins role and still doesn't
>> > run. Ran
>> > the sqlagent with -c -v switches from command line and got foll. 2
>> > errors:
>> >
>> > [298] sql server error 170, line 47: incorrect syntax near 'collate'.
>> >
>> > [sqlstate 42000] (connisloginsysadmin)
>> >
>> > [000] SQLServerAgent must be able to connect to SQLServer as SysAdmin,
>> >
>> > but '(Unknown)' is not a member of the SysAdmin role
>> > Tried the microsoft kb solns and still not running. Need this resolved
>> > urgently. Pls help. Thks. We use sql 2000, sp4.