disconnecting yourself after setting db to single user

Is there a way to drop your connection to a database, specifically after you
have set the database to single user mode?
What I'm trying to do is run a test restore of a SQL Database. What I'm
doing is using the command :
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
to put the db into single user mode, however the problem is that in the
process of doing this it establishes a connection to the db, therefore
taking the one and only available connection to the db. Currently the only
way I can disconnect the connection is to re-start SQL, so I can then run
the restore, and the backup server can successfully connect to the db
(otherwise it complains that it cannot obtain exclusive access to the
database as it is in use).
Needless to say I'd prefer to not need to kill all SQL connections to the
entire server just so I can test the backups are being successful.
KeithWhy not grab the SINGLE_USER for yourself and do the restore.
ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
USE <dbname>
go
RESTORE ...
"Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
> Is there a way to drop your connection to a database, specifically after
> you
> have set the database to single user mode?
> What I'm trying to do is run a test restore of a SQL Database. What I'm
> doing is using the command :
> ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
> to put the db into single user mode, however the problem is that in the
> process of doing this it establishes a connection to the db, therefore
> taking the one and only available connection to the db. Currently the only
> way I can disconnect the connection is to re-start SQL, so I can then run
> the restore, and the backup server can successfully connect to the db
> (otherwise it complains that it cannot obtain exclusive access to the
> database as it is in use).
> Needless to say I'd prefer to not need to kill all SQL connections to the
> entire server just so I can test the backups are being successful.
> Keith
>|||Because I'm using Backup Exec to do the backup and restore, which is a
different connection to the one that I set the db to single user with. The
error below even happens if I set the db to single user from the backup
server itself, rather than on the SQL Server box.
Keith
"Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
news:%23XDGbaetEHA.3788@.TK2MSFTNGP09.phx.gbl...
> Why not grab the SINGLE_USER for yourself and do the restore.
> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> go
> USE <dbname>
> go
> RESTORE ...
>
> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
> news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
> > Is there a way to drop your connection to a database, specifically after
> > you
> > have set the database to single user mode?
> >
> > What I'm trying to do is run a test restore of a SQL Database. What I'm
> > doing is using the command :
> >
> > ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
> >
> > to put the db into single user mode, however the problem is that in the
> > process of doing this it establishes a connection to the db, therefore
> > taking the one and only available connection to the db. Currently the
only
> > way I can disconnect the connection is to re-start SQL, so I can then
run
> > the restore, and the backup server can successfully connect to the db
> > (otherwise it complains that it cannot obtain exclusive access to the
> > database as it is in use).
> >
> > Needless to say I'd prefer to not need to kill all SQL connections to
the
> > entire server just so I can test the backups are being successful.
> >
> > Keith
> >
> >
>|||I don't have much experience with Backup Exec but 1 thing springs to mind.
Restore the database with a different name, once restored drop the original
database then rename the restored one.
"Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
news:OgCTK6etEHA.908@.TK2MSFTNGP10.phx.gbl...
> Because I'm using Backup Exec to do the backup and restore, which is a
> different connection to the one that I set the db to single user with. The
> error below even happens if I set the db to single user from the backup
> server itself, rather than on the SQL Server box.
> Keith
> "Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
> news:%23XDGbaetEHA.3788@.TK2MSFTNGP09.phx.gbl...
>> Why not grab the SINGLE_USER for yourself and do the restore.
>> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
>> go
>> USE <dbname>
>> go
>> RESTORE ...
>>
>> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
>> news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
>> > Is there a way to drop your connection to a database, specifically
>> > after
>> > you
>> > have set the database to single user mode?
>> >
>> > What I'm trying to do is run a test restore of a SQL Database. What I'm
>> > doing is using the command :
>> >
>> > ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
>> >
>> > to put the db into single user mode, however the problem is that in the
>> > process of doing this it establishes a connection to the db, therefore
>> > taking the one and only available connection to the db. Currently the
> only
>> > way I can disconnect the connection is to re-start SQL, so I can then
> run
>> > the restore, and the backup server can successfully connect to the db
>> > (otherwise it complains that it cannot obtain exclusive access to the
>> > database as it is in use).
>> >
>> > Needless to say I'd prefer to not need to kill all SQL connections to
> the
>> > entire server just so I can test the backups are being successful.
>> >
>> > Keith
>> >
>> >
>>
>|||That's basically what I'm doing currently.
With Backup Exec, if you want to restore the db to a database other than the
original, (and since it's just a test, I don't want to mess around with the
live db) you need to have an existing db in place which you can point the
restore job to. Backup Exec then needs to be able to gain exclusive access
to the target db before it can restore the information to it.
Keith
"Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
news:uOdJJKftEHA.2688@.TK2MSFTNGP14.phx.gbl...
> I don't have much experience with Backup Exec but 1 thing springs to mind.
> Restore the database with a different name, once restored drop the
original
> database then rename the restored one.
> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
> news:OgCTK6etEHA.908@.TK2MSFTNGP10.phx.gbl...
> > Because I'm using Backup Exec to do the backup and restore, which is a
> > different connection to the one that I set the db to single user with.
The
> > error below even happens if I set the db to single user from the backup
> > server itself, rather than on the SQL Server box.
> >
> > Keith
> >
> > "Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
> > news:%23XDGbaetEHA.3788@.TK2MSFTNGP09.phx.gbl...
> >> Why not grab the SINGLE_USER for yourself and do the restore.
> >>
> >> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> >> go
> >> USE <dbname>
> >> go
> >> RESTORE ...
> >>
> >>
> >> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
> >> news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
> >> > Is there a way to drop your connection to a database, specifically
> >> > after
> >> > you
> >> > have set the database to single user mode?
> >> >
> >> > What I'm trying to do is run a test restore of a SQL Database. What
I'm
> >> > doing is using the command :
> >> >
> >> > ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK
IMMEDIATE.
> >> >
> >> > to put the db into single user mode, however the problem is that in
the
> >> > process of doing this it establishes a connection to the db,
therefore
> >> > taking the one and only available connection to the db. Currently the
> > only
> >> > way I can disconnect the connection is to re-start SQL, so I can then
> > run
> >> > the restore, and the backup server can successfully connect to the db
> >> > (otherwise it complains that it cannot obtain exclusive access to the
> >> > database as it is in use).
> >> >
> >> > Needless to say I'd prefer to not need to kill all SQL connections to
> > the
> >> > entire server just so I can test the backups are being successful.
> >> >
> >> > Keith
> >> >
> >> >
> >>
> >>
> >
> >
>

Disable Windows Authentication mode in WDA

Hi,
I have installed MS SQL Server 2005 Express Edition, and SQL Web Data
Administrator on my Windows 2003 webserver with IIS 6.0
Everything works fine but I want to deny the users to log on WDA using
Windows Integrated authentication.
At the moment when i open http://ipaddress/webadmin in IE, I get
following textboxes:
Username: SERVERNAME\Administrator (disabled / greyed out)
Password: <blank> (disabled / greyed out)
Server: (local)
and following Radio Buttons:
Authentication Method: Windows Integrated (selected by default)
SQL Login
If I change he Server textbox value from (local) to server\instancename
and click login, it logs me in straight away where I can perform any
admin functions
I dont want any user to log on using windows integrated authentication.
Is there any way around this problem? I dont want my system to get
hacked either, so cant just change default.aspx file and then allow
hackers still to point to my server with some default variable values
and get access
Thanks in advance.
Regards
You cannot disable Windows Authentication mode (:. If you allow the user
logon to the compuer, where SQL Server runs, as administrator, that user can
do anything the SQL Server. Period. So, simply guard that computer's
administrator account as tight as you can. If the you logon the any other
account to the computer, as long as you did not give access to SQL Server to
those user accounts, the SQL Server will be fine. So, you see, if you
developed some solution that uses SQL Server/Express and you delevered it to
your clients. As long as your clients have full right on their computers
(administrator), they can get into the SQL Server/Express you delevered.
<gogaz@.rediffmail.com> wrote in message
news:1161030857.258517.78090@.i42g2000cwa.googlegro ups.com...
> Hi,
> I have installed MS SQL Server 2005 Express Edition, and SQL Web Data
> Administrator on my Windows 2003 webserver with IIS 6.0
> Everything works fine but I want to deny the users to log on WDA using
> Windows Integrated authentication.
> At the moment when i open http://ipaddress/webadmin in IE, I get
> following textboxes:
> Username: SERVERNAME\Administrator (disabled / greyed out)
> Password: <blank> (disabled / greyed out)
> Server: (local)
> and following Radio Buttons:
> Authentication Method: Windows Integrated (selected by default)
> SQL Login
> If I change he Server textbox value from (local) to server\instancename
> and click login, it logs me in straight away where I can perform any
> admin functions
> I dont want any user to log on using windows integrated authentication.
> Is there any way around this problem? I dont want my system to get
> hacked either, so cant just change default.aspx file and then allow
> hackers still to point to my server with some default variable values
> and get access
> Thanks in advance.
> Regards
>
|||Thanks for the reply. I have sorted out the problem now. Actually when
I downloaded WDA from
http://www.microsoft.com/downloads/d...displaylang=en
and installed it on server, it created a folder Samples under
C:\Program Files\Microsoft SQL Server Tools\Microsoft SQL Web Data
Administrator
In that folder I found source code application for WDA. So I opened it
in VS2003 and updated it as per my requirement. Wicked application this
is!!
I will upload it on my server and will post the link here soon I have
updated it further and tested it myself
Regards
Norman Yuan wrote:[vbcol=seagreen]
> You cannot disable Windows Authentication mode (:. If you allow the user
> logon to the compuer, where SQL Server runs, as administrator, that user can
> do anything the SQL Server. Period. So, simply guard that computer's
> administrator account as tight as you can. If the you logon the any other
> account to the computer, as long as you did not give access to SQL Server to
> those user accounts, the SQL Server will be fine. So, you see, if you
> developed some solution that uses SQL Server/Express and you delevered it to
> your clients. As long as your clients have full right on their computers
> (administrator), they can get into the SQL Server/Express you delevered.
> <gogaz@.rediffmail.com> wrote in message
> news:1161030857.258517.78090@.i42g2000cwa.googlegro ups.com...

disable sql 2005 mixed mode password complexity?

I installed SQL Server 2005 for the first time today, and I set it to
mixed mode because that is how we have always had to configure 2000 in
the past for certain applications. I used a weak password for the sa
account, and it caught that, so I changed it.
The problem came when I attempted to install some applications that
previously worked under SQL Server 2000. The first one installed
without errors, but when I attempted to login, there was a connection
error and it said that the database login (which it creates during
install) was invalid. I set the connection properties to use the sa
login, and that worked (as this is a demo, I'm not particularly
concerned with security at the moment).
The next application I attempted to install actually errored out on me,
with some timeout error. When I tried to launch it after the failed
install, I again get connection errors. When I looked at the user
accounts in the Enterprise Manager-like addin (I forget what it's
called), I didn't see any of the accounts which should have been
created during the setup of either application.
Please let me know if I'm way off base here, but what seems to be
happening is that the applications I'm installing use weak passwords
for the SQL user accounts they configure, so they never get created.
Since the accounts are created by an installer, I have no control over
what password they use. So I just need to know if there's some way I
can disable the password complexity check for new accounts and get this
software installed. I'll lecture the developers on security later.You can modify Windows security policy that disable password policy.
"bcochranmsa@.gmail.com"?? ??? ??:

> I installed SQL Server 2005 for the first time today, and I set it to
> mixed mode because that is how we have always had to configure 2000 in
> the past for certain applications. I used a weak password for the sa
> account, and it caught that, so I changed it.
> The problem came when I attempted to install some applications that
> previously worked under SQL Server 2000. The first one installed
> without errors, but when I attempted to login, there was a connection
> error and it said that the database login (which it creates during
> install) was invalid. I set the connection properties to use the sa
> login, and that worked (as this is a demo, I'm not particularly
> concerned with security at the moment).
> The next application I attempted to install actually errored out on me,
> with some timeout error. When I tried to launch it after the failed
> install, I again get connection errors. When I looked at the user
> accounts in the Enterprise Manager-like addin (I forget what it's
> called), I didn't see any of the accounts which should have been
> created during the setup of either application.
> Please let me know if I'm way off base here, but what seems to be
> happening is that the applications I'm installing use weak passwords
> for the SQL user accounts they configure, so they never get created.
> Since the accounts are created by an installer, I have no control over
> what password they use. So I just need to know if there's some way I
> can disable the password complexity check for new accounts and get this
> software installed. I'll lecture the developers on security later.
>|||ALTER LOGIN Username
SET CHECK_POLICY = OFF

disable containter with runtime value does not work

Il try to disable a containter by an expression but it seems not to work (in VS Debug Mode).

I have an variable defined with int value 1

I use the expression !(@.[User::InteresentenAnzahl]>0) to disable the container.

In between i use and select count(*) from table to populate the variable with the record count.

MY problem is - ssis uses always the design time value of the variable and not the runtime value from the variable.

What could be wrong? Is this possible?

I want to disable tasks because my dependency chain require that some later (dependend) tasks need to be executed.

Best Regards

HANNES

Why you want to enable/disable task based on a condition? Perhpars, you need to make a better use of the precedence constraints in the control flow (yes, the green arrows). You can use expression there that would evaluate the value of the variable and decide wheter or not execute task|||

i have a chain of lets say three containters - container one queries the records, countainer two has something to do or not dependend on result of container one (variable) - and countainer tree does something special what need to be done after two.

If I use an expression all later containers does not execute - but if i disable container two contaier three does execute - and thats what i need.

Could you tell me how to configure precedence constraints if the prev container does not execute and the following should execute.

(

this explanation is shorted for the forum - in reall it is processing with some more dependent tasks

1) count new records

2) process add if new records and

3) set last prcessing date (for reporting)

- and processing add needs 20 minutes for zero rows - so why should i execute it) - but I need the last potential processed date to show up how actual data is - this needs to run after a sucessfull process oder if the package runs and no process occured

)

Best regards

hannes

|||

hmayer wrote:

i have a chain of lets say three containters - container one queries the records, countainer two has something to do or not dependend on result of container one (variable) - and countainer tree does something special what need to be done after two.

If I use an expression all later containers does not execute - but if i disable container two contaier three does execute - and thats what i need.

Could you tell me how to configure precedence constraints if the prev container does not execute and the following should execute.

Take a look at this blog post: http://bi-polar23.blogspot.com/2007/05/conditional-task-execution.html

It's not 100% what you're trying to accomplish, but I believe the same technique that worked for me should work for you in this scenario as well.

Basically what you need is an extra container around that thing (be it a task or a container) that you want to disable. Then in the precedence constraint from that new outer container, have the constraint based on an expression. The outer container will execute regardless of whether the inner one does, so the precidence constraints that flow from it will be valid.

And if you need a way to tie a task's execution to an expression but do not know if the task above it will execute, you can add a placeholder Script task above it.

Both of these techniques are demonstrated in this blog post, but please post back here if this doesn't give you all that you need.

|||You should connect the first container to the second and third containers. Also connect the second to the third. Then set the precedence constraints between 1 & 2 with the constraint being between 1 & 3 the converse of 1 & 2.

So, for example:

constraint between 1 & 2 : Execute 2 if myVar == 1
constraint between 1 & 3: Execute 3 if myVar != 1|||

Phil Brammer wrote:

constraint between 1 & 2 : Execute 2 if myVar == 1
constraint between 1 & 3: Execute 3 if myVar != 1

I am wrong? but if for container three containter two and container one is required then 3 does not execute if 2 does not execute because 2 &3 does never report completion!

And if I do not have a constraint between 2 & 3 - 3 gets executed if 2 fails!

|||

hmayer wrote:

Phil Brammer wrote:

constraint between 1 & 2 : Execute 2 if myVar == 1
constraint between 1 & 3: Execute 3 if myVar != 1

I am wrong? but if for container three containter two and container one is required then 3 does not execute if 2 does not execute because 2 &3 does never report completion!

And if I do not have a constraint between 2 & 3 - 3 gets executed if 2 fails!

I do not believe that you are wrong - this is the reason I use "empty" Script tasks as placeholders in this sort of scenario so that there is some task from which a precedence constraint can be originated without adding any "real" functionality to the package.

|||

hmayer wrote:

I am wrong? but if for container three containter two and container one is required then 3 does not execute if 2 does not execute because 2 &3 does never report completion!

And if I do not have a constraint between 2 & 3 - 3 gets executed if 2 fails!

First, you set up an OR condition, not an AND condition on the precedence constraints on #3. Also, you do have a precedence constraint between 2 and 3. There just isn't an expression there. (You can put one there if needed.)

#1 -- myVar==1 --> #2 -> #3
| myVar!=1 --> #3|||

Im sorry, but I did not get it.

IF I Setup OR then

- #3 maybe executed befor two

- and whats more dramatic #3 maybe executed if #2 fails - and thats not allowed

|||

Phil,

I think that approach does not cover the fact 3 should not be executed if 2 fails.

A while ago, In this post I used a similar approach to what Matt describes; in my case I use an empty dataflow as a place holder, so Script task is not the only one you can use.

http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

|||

hmayer wrote:

Im sorry, but I did not get it.

IF I Setup OR then

- #3 maybe executed befor two

- and whats more dramatic #3 maybe executed if #2 fails - and thats not allowed

No. Not true.

SC1 will flow to either SC2 or SC3 depending on the expression and how its evaluated. If the flow goes down the SC2 path, then it will execute SC3 only if SC2 was successful. In this case, the execution of SC1 is irrelevant because of the OR'd precedence constraint. (The expression between SC1 and SC2 would be false in this case).

If the flow goes down the SC3 path because @.myVar != 1, then SC2 will never get executed.

Does this make sense?|||

Hi Refael - thats why i ask.

In addition - why does my original aproach - disable - not work, this sould be fine for all my requirements.

(is it a bug - then i will report it - if it works as design I cannot understand)

Best Regards

HANNES

|||

hmayer wrote:

In addition - why does my original aproach - disable - not work, this sould be fine for all my requirements.

(is it a bug - then i will report it - if it works as design I cannot understand)

I don't know if this is a bug or an underdocumented feature, but it appears that you are not the only one with this issue:

http://www.developerdotstar.com/community/node/345

I've always thought of the Disable property as a design-time tool, not a run-time tool, although I've never seen it officially documented as such.

|||

Rafael Salas wrote:

Phil,

I think that approach does not cover the fact 3 should not be executed if 2 fails.

Are you sure?|||

Phil Brammer wrote:

Rafael Salas wrote:

Phil,

I think that approach does not cover the fact 3 should not be executed if 2 fails.

Are you sure?

Pretty sure...

In this control flow, it looks to me like if Sequence Container 1 succeeds and Sequence Container 2 fails, Sequence Container 3 will still execute. I may be missing something, but I remember fighting with similar approaches when coming up with my "Script task as placeholder) technique.

Can you (you, who have the sample package from which the image was taken ) test it quickly?