Showing posts with label existing. Show all posts
Showing posts with label existing. Show all posts

Sunday, March 11, 2012

Blocking issue in DTS package running on 2005

I'm testing an existing 2000 DTS package in 2005. A Data Transformation Task selects from a view and loads a table that was just truncated. The view definition contains a subselect selecting data off the table,

View: select ... from a, (select ... from b) b2 ON ...

Table to insert into: b

There is blocking going on during the execution of the package. The select * from view (Source) and the insert bulk into the table (Destination) are blocking each other. This does not happen in 2000.

Is there a command that I can put in the view definition subselect that will allow me to just grab the data that it can without worrying about blocking? Would READCOMMITTED or READUNCOMMITED work without impact on the view? Other recommendations?

Has anyone ever run into this?

I found that when you uncheck the Table Lock box of the Data Transformation Task, that this blocking does not occur. Does anyone know why this is required in 2005 but works fine in 2000 with the box checked?

Wednesday, March 7, 2012

blocking

I am trying to create a SQL Profiler trace.
I am confused because the Sessions event class only has the Existing
connection event, and no Connect or Disconnect events.
Why is this? Microsoft support articles like #224453 suggest including the
Connect and Disconnect events in a trace, but I can't find them (or some
others).
Thanks
BillLooks like that article is talking about SQL Server 7.0 Profiler. And the
same is not applicable to SQL Server 2000 Profiler. In SQL Server 2000
Profiler, you should be using the 'Audit Login' and 'Audit Logout' evernts
of the 'Security Audit' event class.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"bille" <belgie@.datamti.com> wrote in message
news:OkCcgHQaEHA.212@.TK2MSFTNGP12.phx.gbl...
> I am trying to create a SQL Profiler trace.
> I am confused because the Sessions event class only has the Existing
> connection event, and no Connect or Disconnect events.
> Why is this? Microsoft support articles like #224453 suggest including
the
> Connect and Disconnect events in a trace, but I can't find them (or some
> others).
> Thanks
> Bill
>|||What you want is under the Security Audit Event Class. Look for Audit Login
and Audit Logout
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"bille" <belgie@.datamti.com> wrote in message
news:OkCcgHQaEHA.212@.TK2MSFTNGP12.phx.gbl...
> I am trying to create a SQL Profiler trace.
> I am confused because the Sessions event class only has the Existing
> connection event, and no Connect or Disconnect events.
> Why is this? Microsoft support articles like #224453 suggest including
the
> Connect and Disconnect events in a trace, but I can't find them (or some
> others).
> Thanks
> Bill
>

Sunday, February 19, 2012

blank query window- how to get?

In Sql Server Query Analyzer, to get a blank query window, it seems that I
must first edit an existing stored procedure, at which point the "New
window" button is enabled.
Is there a way to get an empty window without first having to edit another
query? If I launch Query Analyzer, I have an empty instance of the app..how
do I get a blank query window?
thx1. You must be connected to an instance of sqlserver.
2. [ctrl]+[N] should open a new window/connection to the connected instance.
-oj
"mrmagoo" <-> wrote in message news:eO$AWA6TGHA.4956@.TK2MSFTNGP09.phx.gbl...
> In Sql Server Query Analyzer, to get a blank query window, it seems that I
> must first edit an existing stored procedure, at which point the "New
> window" button is enabled.
> Is there a way to get an empty window without first having to edit another
> query? If I launch Query Analyzer, I have an empty instance of the
> app..how
> do I get a blank query window?
> thx
>|||that's my point...it doesn't.
I launch query analyzer, and pick the server. It connects.
By default it does open an empty query window. If I close that, there's no
way that I can see to get another one unless I edit another stored
procedure. THEN I can Ctrl + N to get as many new windows as I want.
"oj" <nospam_ojngo@.home.com> wrote in message
news:udqvtV6TGHA.224@.TK2MSFTNGP10.phx.gbl...
> 1. You must be connected to an instance of sqlserver.
> 2. [ctrl]+[N] should open a new window/connection to the connected
instance.
> --
> -oj
>
> "mrmagoo" <-> wrote in message
news:eO$AWA6TGHA.4956@.TK2MSFTNGP09.phx.gbl...
I
another
>|||When you close the last window, there is nothing left in "query window"
cache to use to reinitiate a new connection for another query window. This
is by design and would not be enhanced - QA is now replaced with Management
Studio (sqlwb) in sql2k5.
-oj
"mrmagoo" <-> wrote in message news:eGUfXo6TGHA.4740@.TK2MSFTNGP14.phx.gbl...
> that's my point...it doesn't.
> I launch query analyzer, and pick the server. It connects.
> By default it does open an empty query window. If I close that, there's no
> way that I can see to get another one unless I edit another stored
> procedure. THEN I can Ctrl + N to get as many new windows as I want.
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:udqvtV6TGHA.224@.TK2MSFTNGP10.phx.gbl...
> instance.
> news:eO$AWA6TGHA.4956@.TK2MSFTNGP09.phx.gbl...
> I
> another
>|||ok..thanks...
"oj" <nospam_ojngo@.home.com> wrote in message
news:eGk2Jk8TGHA.4900@.TK2MSFTNGP12.phx.gbl...
> When you close the last window, there is nothing left in "query window"
> cache to use to reinitiate a new connection for another query window. This
> is by design and would not be enhanced - QA is now replaced with
Management
> Studio (sqlwb) in sql2k5.
> --
> -oj
>
> "mrmagoo" <-> wrote in message
news:eGUfXo6TGHA.4740@.TK2MSFTNGP14.phx.gbl...
no
>|||"mrmagoo" <-> wrote in message news:eO$AWA6TGHA.4956@.TK2MSFTNGP09.phx.gbl...
> In Sql Server Query Analyzer, to get a blank query window, it seems that I
> must first edit an existing stored procedure, at which point the "New
> window" button is enabled.
> Is there a way to get an empty window without first having to edit another
> query? If I launch Query Analyzer, I have an empty instance of the
> app..how
> do I get a blank query window?
> thx
>
CTRL+O
or
Select File / Connect...
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
--|||thanks!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:eYLv$gEUGHA.2444@.TK2MSFTNGP14.phx.gbl...
> "mrmagoo" <-> wrote in message
news:eO$AWA6TGHA.4956@.TK2MSFTNGP09.phx.gbl...
I
another
> CTRL+O
> or
> Select File / Connect...
> --
> 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
> --
>

Blank password check

Is there any way to check whether the password for existing sql authentication login is blank? (except trying to connect)

Where can I find information about how the passwords are stored internally in SQL Server?

which version r u talking about. if its 2005 you can do that i believe.

credentials are stored in sysxlogins system table

Madhu

|||Only hashes are stored, you won′t find them in clear text. For SQL Server 2005 use password policies to ensure a certain password complexity.

Jens K. Suessmeyer:


http://www.sqlserver2005.de

|||Yes I understand it, I just wonder how some tools discover logins with blank passwords (http://www.ngssoftware.com/products/database-security/ngs-squirrel-sql.php)|||

Oh, actually it seems to be fairly simple,

I just call ALTER LOGIN myLogin WITH PASSWORD = N'' OLD_PASSWORD = N'' and if it succeed -- login myLogin has a blank password. Or am I missing something?

|||

sir..... you are resetting the password to null. it will change the password, it will not tell u whether its blank or not. But it will make the password blank evenif its not blank. Not atall the correct method

Madhu

|||No, trying to change the password seems to be a bit offensive :-)

I wrote this short code snippet for you, tell me if it works for you, it scans all reachable /discoverable servers and test all SQL Server logins:

Code Snippet

foreach (DataRow dr in SmoApplication.EnumAvailableSqlServers().Rows)

{

Server srv = new Server(dr["Name"].ToString());

foreach (Login l in srv.Logins)

{

if (l.LoginType == LoginType.SqlLogin)

{

SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog=master;User Id={1},Password={2}",

srv.InstanceName,

l.Name,

string.Empty));

try

{

conn.Open();

Console.WriteLine(string.Format("The userid {0} has an empty password.", l.Name));

}

catch (SqlException ex)

{ }

finally

{

if (conn.State == ConnectionState.Open)

conn.Close();

}

}

}

}

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thanks Jens,

the way you suggest works fine I think.

|||

Madhu,

I cannot agree with you.

See the following script:

Code Snippet

CREATE LOGIN myLogin WITH PASSWORD = N'IT IS NOT A BLANK PASSWORD!', CHECK_POLICY = OFF

BEGIN TRY

-- this statement will FAIL because we specified invalid old (current) password

ALTER LOGIN myLogin WITH PASSWORD = N'' OLD_PASSWORD = N''

END TRY

BEGIN CATCH

PRINT 'Password is NOT blank!'

END CATCH

-- this statement will actually change the password

ALTER LOGIN myLogin WITH PASSWORD = N''

PRINT '//Password was changed to blank//'

-- so now password is blank

BEGIN TRY

-- this statement will SUCCEED because we specified right old (current) password

ALTER LOGIN myLogin WITH PASSWORD = N'' OLD_PASSWORD = N''

-- but actually nothing will happen because new password is the same as old one.

PRINT 'Password is BLANK!'

END TRY

BEGIN CATCH

END CATCH

DROP LOGIN myLogin

The idea is that ALTER LOGIN fails when called with OLD_PASSWORD option and invalid old (current) password given.

I think that it is even more effective way to check than trying to logon on server with <loginName, blankPassword> credentials.

Blank password check

Is there any way to check whether the password for existing sql authentication login is blank? (except trying to connect)

Where can I find information about how the passwords are stored internally in SQL Server?

which version r u talking about. if its 2005 you can do that i believe.

credentials are stored in sysxlogins system table

Madhu

|||Only hashes are stored, you won′t find them in clear text. For SQL Server 2005 use password policies to ensure a certain password complexity.

Jens K. Suessmeyer:


http://www.sqlserver2005.de

|||Yes I understand it, I just wonder how some tools discover logins with blank passwords (http://www.ngssoftware.com/products/database-security/ngs-squirrel-sql.php)|||

Oh, actually it seems to be fairly simple,

I just call ALTER LOGIN myLogin WITH PASSWORD = N'' OLD_PASSWORD = N'' and if it succeed -- login myLogin has a blank password. Or am I missing something?

|||

sir..... you are resetting the password to null. it will change the password, it will not tell u whether its blank or not. But it will make the password blank evenif its not blank. Not atall the correct method

Madhu

|||No, trying to change the password seems to be a bit offensive :-)

I wrote this short code snippet for you, tell me if it works for you, it scans all reachable /discoverable servers and test all SQL Server logins:

Code Snippet

foreach (DataRow dr in SmoApplication.EnumAvailableSqlServers().Rows)

{

Server srv = new Server(dr["Name"].ToString());

foreach (Login l in srv.Logins)

{

if (l.LoginType == LoginType.SqlLogin)

{

SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog=master;User Id={1},Password={2}",

srv.InstanceName,

l.Name,

string.Empty));

try

{

conn.Open();

Console.WriteLine(string.Format("The userid {0} has an empty password.", l.Name));

}

catch (SqlException ex)

{ }

finally

{

if (conn.State == ConnectionState.Open)

conn.Close();

}

}

}

}

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thanks Jens,

the way you suggest works fine I think.

|||

Madhu,

I cannot agree with you.

See the following script:

Code Snippet

CREATE LOGIN myLogin WITH PASSWORD = N'IT IS NOT A BLANK PASSWORD!', CHECK_POLICY = OFF

BEGIN TRY

-- this statement will FAIL because we specified invalid old (current) password

ALTER LOGIN myLogin WITH PASSWORD = N'' OLD_PASSWORD = N''

END TRY

BEGIN CATCH

PRINT 'Password is NOT blank!'

END CATCH

-- this statement will actually change the password

ALTER LOGIN myLogin WITH PASSWORD = N''

PRINT '//Password was changed to blank//'

-- so now password is blank

BEGIN TRY

-- this statement will SUCCEED because we specified right old (current) password

ALTER LOGIN myLogin WITH PASSWORD = N'' OLD_PASSWORD = N''

-- but actually nothing will happen because new password is the same as old one.

PRINT 'Password is BLANK!'

END TRY

BEGIN CATCH

END CATCH

DROP LOGIN myLogin

The idea is that ALTER LOGIN fails when called with OLD_PASSWORD option and invalid old (current) password given.

I think that it is even more effective way to check than trying to logon on server with <loginName, blankPassword> credentials.