Authentication

Hi! I have a problem enabling access to my 'sa' login to a couple of
application db's it fails each time. I think the problem lies herein:I am on
Sql 2005 BTW...When I go to server properties/security it shows,
appropriately, Mixed mode authentication. However, when I click on "View
Connection Properties" in the connection section of the server properties
page it shows windows authentiication. Therefore, I believe that when I try
to add the db's to the mapping for 'sa' the server is looking for a windows
account which of course does not exist. Any advice as to how I might proceed?
Thanks all...I do not want to create a windows account for sa...Adios...
Well, 'view connection properties' refers to your current conn props... so
that just means that you're connecting to the server with windows auth... it
has nothing to do with the server security setting.
Since you didn't really provide an error message I'll say one thing to check
would be whether sa owns the DB in question. You can't add an acct as a user
if that acct already owns the DB.
"Walt Herman" wrote:

> Hi! I have a problem enabling access to my 'sa' login to a couple of
> application db's it fails each time. I think the problem lies herein:I am on
> Sql 2005 BTW...When I go to server properties/security it shows,
> appropriately, Mixed mode authentication. However, when I click on "View
> Connection Properties" in the connection section of the server properties
> page it shows windows authentiication. Therefore, I believe that when I try
> to add the db's to the mapping for 'sa' the server is looking for a windows
> account which of course does not exist. Any advice as to how I might proceed?
> Thanks all...I do not want to create a windows account for sa...Adios...
|||If you install SQL Server in Windows Authentication Mode, then change to
Mixed Authentication Mode, the sa account is disabled.
Use ALTER LOGIN to enable the account. For more information, see the topic
How to: Change Server Authentication Mode in Books Online.
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Walt Herman" <WaltHerman@.discussions.microsoft.com> wrote in message
news:4C4AED35-F285-477F-BE3C-8961098826E8@.microsoft.com...
> Hi! I have a problem enabling access to my 'sa' login to a couple of
> application db's it fails each time. I think the problem lies herein:I am
> on
> Sql 2005 BTW...When I go to server properties/security it shows,
> appropriately, Mixed mode authentication. However, when I click on "View
> Connection Properties" in the connection section of the server properties
> page it shows windows authentiication. Therefore, I believe that when I
> try
> to add the db's to the mapping for 'sa' the server is looking for a
> windows
> account which of course does not exist. Any advice as to how I might
> proceed?
> Thanks all...I do not want to create a windows account for sa...Adios...

audit single user mode

Any way to audit when/and by whom a DB has been changed to single user mode?
We have a couple of DB's that are being locked, and it appears to be some
process. Nothing shows up in SQL logs / event logs indicating the change.
Any way I can turn on some kind of verbose logging or set some kind of
trigger to watch for this? We think there is some process doing it, but
without a security context and/or time stamp of exactly when it happens it's
been quite difficult to track this event down. Help! Chuck
My bet is that you have a maintenance job for integrity checking that has
the 'Repair minor problems' option checked. This will put the database into
single-user mode if it finds any errors and tries to repair them. You
shouldn't run with this option on as its always better to investigate any
errors rather than blindly repairing them.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:88AAB265-3719-4D97-AD08-E61D20DC83B8@.microsoft.com...
> Any way to audit when/and by whom a DB has been changed to single user
mode?
> We have a couple of DB's that are being locked, and it appears to be some
> process. Nothing shows up in SQL logs / event logs indicating the change.
> Any way I can turn on some kind of verbose logging or set some kind of
> trigger to watch for this? We think there is some process doing it, but
> without a security context and/or time stamp of exactly when it happens
it's
> been quite difficult to track this event down. Help! Chuck

Attaching 2000 dbs to 2005

I installed ss2005 on a new machine. I try to attach a 2000 db and it found
an error (something in a view that it didn't like) and aborted the import.
I think it aborted on finding the first error but I'm not sure.
So, am I dead in the water? Do I have to take my db back to ss2000, deal
with the error and try a ss2005 atach again? If there are a thousand errors
would I have to do this a thousand times?
What If I just tried a restore' What's the best way to approach this
problem?
Many thanks, Tinn ManTinn
If I remember well , there is na upgrade advisor to make sure that sql
server 2005 'likes' everything
Also, I'd recommend you to backup database first and then restore it on sql
server 2005
"Tinn Man" <tm@.nospammeexcite.com> wrote in message
news:Oao7332hGHA.4404@.TK2MSFTNGP05.phx.gbl...
>I installed ss2005 on a new machine. I try to attach a 2000 db and it
>found an error (something in a view that it didn't like) and aborted the
>import. I think it aborted on finding the first error but I'm not sure.
> So, am I dead in the water? Do I have to take my db back to ss2000, deal
> with the error and try a ss2005 atach again? If there are a thousand
> errors would I have to do this a thousand times?
> What If I just tried a restore' What's the best way to approach this
> problem?
> Many thanks, Tinn Man
>

Attaching 2000 dbs to 2005

I installed ss2005 on a new machine. I try to attach a 2000 db and it found
an error (something in a view that it didn't like) and aborted the import.
I think it aborted on finding the first error but I'm not sure.
So, am I dead in the water? Do I have to take my db back to ss2000, deal
with the error and try a ss2005 atach again? If there are a thousand errors
would I have to do this a thousand times?
What If I just tried a restore' What's the best way to approach this
problem?
Many thanks, Tinn ManTinn
If I remember well , there is na upgrade advisor to make sure that sql
server 2005 'likes' everything
Also, I'd recommend you to backup database first and then restore it on sql
server 2005
"Tinn Man" <tm@.nospammeexcite.com> wrote in message
news:Oao7332hGHA.4404@.TK2MSFTNGP05.phx.gbl...
>I installed ss2005 on a new machine. I try to attach a 2000 db and it
>found an error (something in a view that it didn't like) and aborted the
>import. I think it aborted on finding the first error but I'm not sure.
> So, am I dead in the water? Do I have to take my db back to ss2000, deal
> with the error and try a ss2005 atach again? If there are a thousand
> errors would I have to do this a thousand times?
> What If I just tried a restore' What's the best way to approach this
> problem?
> Many thanks, Tinn Man
>

Attached DBs coming online as READ-ONLY - SQL Server 2000 on Server 2003 R2

Had a server that went belly up. ALL database files were on the SAN and were "supposedly" ok.

We rebuilt the server to have Windows Advanced Server 2003 R2. Install went fine.

Installed SQL Server 2000 SP4 + latest builds to 2187 and SQL Server 2005 SP1.
That all went well.

We attached a few of our DBs in SS2K and ALL of them are coming up in read only mode. We try to put then in normal mode my unchecking the read only option on the properties of the DB but we get the following error:

ERROR 5105: Device activation error. The physical file named 'D:\myDB.mdf' may be incorrect.
Device activation error. The physical file named 'D:\myDB.ldf' may be incorrect.
Could not restart database 'myDB'. Reverting back to old status.
ALTER DATABASE statement failed.
Device activation error. The physical file named 'D:\myDB.ldf' may be incorrect.
sp_dboption failed.

The D: drive in this example is a LOCAL drive. I moved this DB from the SAN. The MDF was on on volume and the LDF was on another volume.

I did receieve the same error when the database files were in the SAN locations.

I thought by moving both the MDF and LDF to a local drive I could elimitate the possibility that the SAN was causing the issue BUT that seems not to be the case.

The account being used for the SQL Server services is a Domain Admin account that has also been added to the Admins group on the server.

All persmission are set and look good on the shares and volumes.

As a side note, I copied over a small test DB from another server (copied the MDF & LDF).

I attached the DB and IT TOO attached in a READ-ONLY mode.

Thanks ALL!

I have the same error|||Damn thing was all caused by a faulty SCSI card - DOH!!!

I am by means NO Systems Engineer BUT from what I was relayed here is what happened:

The throughput of the connection to the SAN is usually running at 320 mbits. We found that our throughput was at 80 mbits, only 75% of the throughput the "system" knows it should see.

Now I am told applications query this throughput metric the System has stored away and expects data to be pulled/pushed at a rate in ms that corresponds to the last know throughput speed. So let’s say for 320 mbits response time should be .001 ms or so with some acceptable deviation.

Of course the ms number I threw out is made up (I will see if I can get the real mbits/ms ratios later).

SO ... when SQL Server was given the command to attach the DB files which resided on the SAN ... the throughput speed was not what SQL Server was expecting and it took longer to attach the files and in some manner put the DBs in READ_ONLY mode.

In saying this though ... I still have one question my SE is tracking down and one question I have M$ chewing on and they are:

1. Since the locals drives (C: & D:) ARE NOT on the SCSI
controller and cable ... why did the DBs I attached
from those drives also come up READ_ONLY

2. FOR M$: If this is indeed a SCSI controller/cable
issue ... what caused the attached DBs to go into
READ_ONLY mode.

Aside from that ... the DBs are now attaching and are online and are not READ_ONLY!

GO FIGURE!

I will post updates as I gather more pertinent details.

Attach/restore databases in SQL Server 2000

Hi,

I've got a question regarding attaching/restoring of DBs and wonder if anyone could help me out.

Does SQL Server 2000 provide a functionality to attach/restore DB automatically? ie, some kind of polling service to attach/restore DB that were detached previously?

Thanks

Danny.

No.

And why would it. There could be dozens of experimental, sample, demo, 'old' database files on the computer. It should not ATTACH anything that it is not specifically instructed to attach.

|||

why not?

Imagine the scenerio where there are 100 salesman with a laptop each and each laptop running a local version of web portal supported by local MSDE backend (only relevant database will be on the machine).

A WISE script executable will be created for updates of portal and existing database. If there's a new database required, the server should be able to be aware of it an attach it automatically.

Surely there's got to be a way of attaching database to 100 machines automatically.

|||

You can write scripts, you can even write stored procedures that automatically execute when the server first starts, and you can use Replication.

But if you find an 'automatic' way to attach databases, please let us know.

|||

Will certainly do. "Inside SQL Server 2000" just arrived. I'll consult it instead.

|||

SQL Server has no way for automatic / implicit attaching of databases. You will have to use (as Anrie indicated a SP or task to search for files). Why don′t you put that in your installer script ? As from your description, it is running on the machine where the databases reside on.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

attach/ detach of dbs

hi,
Is it possible to attach and detach databases
participating in replication?
regards,
bharath| hi,
|
| Is it possible to attach and detach databases
| participating in replication?
|
| regards,
| bharath
--
Hi Bharath,
From SQL Server books online:
"You cannot detach a database while it is being replicated."
May I ask what your specific requirements are?
--
Eric Cárdenas
SQL Server support|||HiEric,
The requirement is simple. I need to pass on the physical data and log files of one database participating in replication to another server( managed by another group). I do not want other databases to be brought down as well (shut down of sql). That is why this question arose...
>--Original Message--
>| hi,
>| >| Is it possible to attach and detach databases >| participating in replication?
>| >| regards,
>| bharath
>--
>Hi Bharath,
>From SQL Server books online:
>"You cannot detach a database while it is being replicated."
>May I ask what your specific requirements are?
>--
>Eric C=E1rdenas
>SQL Server support
>.
>||||
| HiEric,
| The requirement is simple. I need to pass on the physical
| data and log files of one database participating in
| replication to another server( managed by another group).
| I do not want other databases to be brought down as well
| (shut down of sql). That is why this question arose...
--
Hi Bharath,
How about backing up the subscriber/publisher database participating in the
replication? You can then restore that backup to another server and you
have the same data. The mdf/ldf will be created for you when you restore.
Would this be a suitable workaround for you?
Hope this helps,
--
Eric Cárdenas
SQL Server support

Attach remote DB file

Hi,
is there a way to attach a DB's file locate on a remote path ?
why SQL Server allows attach only from local drives ?
Thanks
Adriano
Adry
There is a TRACE ( don't remember a number) which allows you to create a
database to be stored on remote machine
But it is strongly not recommended , I hope you know why.
Copy the file to the local disk an attach them
"Adry" <adry@.ij_nospam.it> wrote in message
news:%23Q01VRFOGHA.3924@.TK2MSFTNGP14.phx.gbl...
> Hi,
> is there a way to attach a DB's file locate on a remote path ?
> why SQL Server allows attach only from local drives ?
>
> Thanks
> Adriano
>
|||Hi,
what do you mean by "there is a TRACE" ? what is "a TRACE" ?
Please, can you explain me better ?
Thanks
Adriano
"Uri Dimant" <urid@.iscar.co.il> ha scritto nel messaggio
news:uW22DhFOGHA.3264@.TK2MSFTNGP11.phx.gbl...
> Adry
> There is a TRACE ( don't remember a number) which allows you to create a
> database to be stored on remote machine
> But it is strongly not recommended , I hope you know why.
> Copy the file to the local disk an attach them
>
>
>
> "Adry" <adry@.ij_nospam.it> wrote in message
> news:%23Q01VRFOGHA.3924@.TK2MSFTNGP14.phx.gbl...
>
|||Sorry
By default, you cannot create a database file on a mapped or UNC network
location under SQL Server 2000. You can get around this by turning on trace
flag 1807
DBCC TRACEON (1807)
I dont think it is documented as well as supported but I know for sure it is
not recommended
"Adry" <adry@.ij_nospam.it> wrote in message
news:eYQsayFOGHA.3732@.TK2MSFTNGP10.phx.gbl...
> Hi,
> what do you mean by "there is a TRACE" ? what is "a TRACE" ?
> Please, can you explain me better ?
> Thanks
> Adriano
> "Uri Dimant" <urid@.iscar.co.il> ha scritto nel messaggio
> news:uW22DhFOGHA.3264@.TK2MSFTNGP11.phx.gbl...
>
|||Adry wrote:
> Hi,
> what do you mean by "there is a TRACE" ? what is "a TRACE" ?
> Please, can you explain me better ?
> Thanks
> Adriano
> "Uri Dimant" <urid@.iscar.co.il> ha scritto nel messaggio
> news:uW22DhFOGHA.3264@.TK2MSFTNGP11.phx.gbl...
>
As Uri already pointed out, it's not recommended/supported to have a
database file on a remote location. If you still decide to do it, you
should be aware of the consequences in terms of performance and
database integrity.
You can read more about it here -
http://support.microsoft.com/default...b;en-us;304261
Regards
Steen
|||Adry wrote:
> why SQL Server allows attach only from local drives ?
>
Because a database located across a network will suffer poor
performance and poor resilience/availability. For that reason,
attaching on a remote drive isn't supported.
Why would you want to attach a database on a remote drive?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx