Connection Pooling & Users

I have an intranet application that comprises an ASP.Net application
connecting to a SQL Server DB
The application has 150 users. At the moment I am connecting using the
following setup
I have created a MyApp_WebUser user in SQL Server. My connection string,
which is stored in my web.config file, is
<add key="DB:CSA" value="data source=MyServer;initial
catalog=CSA;uid=MyApp_WebUser;pwd=123456
78" />
and by business objects use this string to make all its connections to the
database. This way, all my connections strings are identical and I can make
use of connection pooling.
However, this is causing some problems in the business domain. My DB
contains a User table in which I have a record of all users, their
application login and their application password. When users log in via a
login page, their username and password are checked against the User table
and they get access to various pages according to their role (I also have a
Role table). There are a number of issues that I need to address here and
would appreciate advice
1) It is a business requirement that all passwords need to change every 30
days and they need to conform to a particular pattern. I figure I could
either, write my own code to enforce this in the application or allow users
to log in using their active directory passwords.
If I use their windows login I would have the advantage of letting Windows
deal with the changing of passwords etc, but would I be right in thinking
that connection string should be changed to
<add key="DB:CSA" value="data source=MyServer;initial
catalog=CSA;integrated security = SSPI" />.
If this is the case, then my connection string will be different for each
user and I will not be able to take advantage of connection pooling
2) If I stick with my User table and write my own code for enforcing the
password requirements, how could I store their passwords in the db in an
encrypted format?
Advice appreciated
Terry HollandAlso
If I do store my connection string with UserID & Password in web.config
file, how secure is thisd?
Terry|||Hi Terry,
Reposnses below:

> If I use their windows login I would have the advantage of letting Windows
> deal with the changing of passwords etc, but would I be right in thinking
> that connection string should be changed to
NO - you are simply changing your authentication mechanism (you might
want to retain the functionality for roles and basically retain your
user and role tables too). Instead of checking the user name password
against the database, you can use Directory Services to test it against
an active directory (do not use integrated authentication, for minimum
impact to your current application). You will need to change ONLY one
function (the one which checks for the username password in the
database).
The Connection string is totally independent of the users of your
application. It can stay exactly as it is now.

> 2) If I stick with my User table and write my own code for enforcing the
> password requirements, how could I store their passwords in the db in an
> encrypted format?
The approach mentioned above will let you use the active directoy
policy, so you won't have to write your own code, but for future
purposes, you should always Hash (instead of encrypting) your
passwords. There is a function available in the FormsAuthentication
class (i think) which allows you to do that.
Third - the database connection string in your web.config file:
- If you want to keep it there, keep it in some encrypted form (instead
of plain text) and decrypt it before using. So you could decrypt it and
cache it for your application to use, every so often.
Regards,
Vaibhav|||What you use for authentication to your application, and what you use to
connect to the database are not related. You can use integrated for
authentication to your application - that has no bearing on your connection
string to the database. So you can use integrated, or not.
Btw, just using integrated in your connection string, means that the
connection will be made as the ASPNET user (or whatever ASP.NET is running
under on that machine). It does not mean the credentials of the current user
of your application will be used. You can have it pass the credentials of
the user with a Web.config setting to tell it to impersonate the current
user.
If you want to roll your own security, .NET provides many encryption
implementation for you to chose from. Check out the
System.Security.Cryptography namespace.
"Terry Holland" <TerryHolland@.Community.nospam> wrote in message
news:OZrjiq2cGHA.5016@.TK2MSFTNGP04.phx.gbl...
>I have an intranet application that comprises an ASP.Net application
> connecting to a SQL Server DB
> The application has 150 users. At the moment I am connecting using the
> following setup
> I have created a MyApp_WebUser user in SQL Server. My connection string,
> which is stored in my web.config file, is
> <add key="DB:CSA" value="data source=MyServer;initial
> catalog=CSA;uid=MyApp_WebUser;pwd=123456
78" />
> and by business objects use this string to make all its connections to the
> database. This way, all my connections strings are identical and I can
> make
> use of connection pooling.
> However, this is causing some problems in the business domain. My DB
> contains a User table in which I have a record of all users, their
> application login and their application password. When users log in via a
> login page, their username and password are checked against the User table
> and they get access to various pages according to their role (I also have
> a
> Role table). There are a number of issues that I need to address here and
> would appreciate advice
> 1) It is a business requirement that all passwords need to change every 30
> days and they need to conform to a particular pattern. I figure I could
> either, write my own code to enforce this in the application or allow
> users
> to log in using their active directory passwords.
> If I use their windows login I would have the advantage of letting Windows
> deal with the changing of passwords etc, but would I be right in thinking
> that connection string should be changed to
> <add key="DB:CSA" value="data source=MyServer;initial
> catalog=CSA;integrated security = SSPI" />.
> If this is the case, then my connection string will be different for each
> user and I will not be able to take advantage of connection pooling
> 2) If I stick with my User table and write my own code for enforcing the
> password requirements, how could I store their passwords in the db in an
> encrypted format?
>
> Advice appreciated
> Terry Holland
>|||Terry,
Not quite...
If you use the active directory to login your users your application may
still utilize integrated security for the sql connection and it won't run as
each individual user unless you put <identity impersonate="true" /> into the
web.config file. Instead the integrated security connection to sql server
will use the ASPNET account that the website is running then .net code with.
At this point which user account your web application will use to connect to
sql server and how you should treat such will differ based on which version
of IIS you are using and based on which version of asp.net you are using.
Could you let us know which versions you're working with?
Regards,
S. Justin Gengo
Web Developer / Programmer
Free code library:
http://www.aboutfortunate.com
"Out of chaos comes order."
Nietzsche
"Terry Holland" <TerryHolland@.Community.nospam> wrote in message
news:OZrjiq2cGHA.5016@.TK2MSFTNGP04.phx.gbl...
>I have an intranet application that comprises an ASP.Net application
> connecting to a SQL Server DB
> The application has 150 users. At the moment I am connecting using the
> following setup
> I have created a MyApp_WebUser user in SQL Server. My connection string,
> which is stored in my web.config file, is
> <add key="DB:CSA" value="data source=MyServer;initial
> catalog=CSA;uid=MyApp_WebUser;pwd=123456
78" />
> and by business objects use this string to make all its connections to the
> database. This way, all my connections strings are identical and I can
> make
> use of connection pooling.
> However, this is causing some problems in the business domain. My DB
> contains a User table in which I have a record of all users, their
> application login and their application password. When users log in via a
> login page, their username and password are checked against the User table
> and they get access to various pages according to their role (I also have
> a
> Role table). There are a number of issues that I need to address here and
> would appreciate advice
> 1) It is a business requirement that all passwords need to change every 30
> days and they need to conform to a particular pattern. I figure I could
> either, write my own code to enforce this in the application or allow
> users
> to log in using their active directory passwords.
> If I use their windows login I would have the advantage of letting Windows
> deal with the changing of passwords etc, but would I be right in thinking
> that connection string should be changed to
> <add key="DB:CSA" value="data source=MyServer;initial
> catalog=CSA;integrated security = SSPI" />.
> If this is the case, then my connection string will be different for each
> user and I will not be able to take advantage of connection pooling
> 2) If I stick with my User table and write my own code for enforcing the
> password requirements, how could I store their passwords in the db in an
> encrypted format?
>
> Advice appreciated
> Terry Holland
>|||Thank you.
I have successfully re-written the function to authenticate users using
Directory Services
"V" <vaibhav.gadodia@.gmail.com> wrote in message
news:1147182064.446995.240040@.j73g2000cwa.googlegroups.com...
> Hi Terry,
> Reposnses below:
>
Windows[vbcol=seagreen]
thinking[vbcol=seagreen]
> NO - you are simply changing your authentication mechanism (you might
> want to retain the functionality for roles and basically retain your
> user and role tables too). Instead of checking the user name password
> against the database, you can use Directory Services to test it against
> an active directory (do not use integrated authentication, for minimum
> impact to your current application). You will need to change ONLY one
> function (the one which checks for the username password in the
> database).
> The Connection string is totally independent of the users of your
> application. It can stay exactly as it is now.
>
> The approach mentioned above will let you use the active directoy
> policy, so you won't have to write your own code, but for future
> purposes, you should always Hash (instead of encrypting) your
> passwords. There is a function available in the FormsAuthentication
> class (i think) which allows you to do that.
> Third - the database connection string in your web.config file:
> - If you want to keep it there, keep it in some encrypted form (instead
> of plain text) and decrypt it before using. So you could decrypt it and
> cache it for your application to use, every so often.
> Regards,
> Vaibhav
>|||Thanks for Vaibhav's informative inputs.
Hi Terry,
I don't think you should change the authenitcation to windows since that'll
different much from your original application design. I think you should
manually do the password change policy in your application. For the
password stored in your custom db, generally, it is recommended to store
password hash rather than clear text to make it securer...
as for data connection string in web.config, ASP.NET has provided
encryption support when deploying the application, here are the msdn
articles discussing on this:
#How To: Encrypt Configuration Sections in ASP.NET 2.0 Using DPAPI
http://msdn.microsoft.com/library/e...05.asp?frame=tr
ue
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Connection Management

Hi:

I have a Point-of-sale application that uses SQL Server2000 for the backend.
Basically, the users perform various functions boiling down to login (check
password from a table) and data entry (insert a food entry). Previously, I
would open a new ADO 2.7 connection to the database each time one of these
types of database accessing functions needed to be performed - but I noticed
that sometimes the DB would freeze the application for 20 seconds or so - or
even cause a timeout error.

To fix this, I open a DB connection when the application first starts,
keeping it open for the life of the application - each time a function needs
to access the DB, it just uses the applications (global) connection that is
constantly open and connected.

This seems to have fixed the problem, however, I am curious, is this an OK
way to handle the connections - keeping in mind that there are four separate
stations - each running the application - at the same time. Therefore, I
have 4 constantly open connections at the same time.

Thanks and regards,

Ryan Kennedy"Ryan P. Kennedy" <ryanp.kennedy@.verizon.net> wrote in message
news:lYnBb.4549$UM4.1037@.nwrdny01.gnilink.net...
> Hi:
> I have a Point-of-sale application that uses SQL Server2000 for the
backend.
> Basically, the users perform various functions boiling down to login
(check
> password from a table) and data entry (insert a food entry). Previously,
I
> would open a new ADO 2.7 connection to the database each time one of these
> types of database accessing functions needed to be performed - but I
noticed
> that sometimes the DB would freeze the application for 20 seconds or so -
or
> even cause a timeout error.
> To fix this, I open a DB connection when the application first starts,
> keeping it open for the life of the application - each time a function
needs
> to access the DB, it just uses the applications (global) connection that
is
> constantly open and connected.
> This seems to have fixed the problem, however, I am curious, is this an OK
> way to handle the connections - keeping in mind that there are four
separate
> stations - each running the application - at the same time. Therefore, I
> have 4 constantly open connections at the same time.
>
> Thanks and regards,
> Ryan Kennedy

I don't know much about ADO, but it sounds like you're describing a form of
connection pooling, which is certainly a very common way to manage
connections from multiple clients.

Simon|||"Ryan P. Kennedy" <ryanp.kennedy@.verizon.net> wrote in message
news:lYnBb.4549$UM4.1037@.nwrdny01.gnilink.net...
> Hi:
> I have a Point-of-sale application that uses SQL Server2000 for the
backend.
> Basically, the users perform various functions boiling down to login
(check
> password from a table) and data entry (insert a food entry). Previously,
I
> would open a new ADO 2.7 connection to the database each time one of these
> types of database accessing functions needed to be performed - but I
noticed
> that sometimes the DB would freeze the application for 20 seconds or so -
or
> even cause a timeout error.
> To fix this, I open a DB connection when the application first starts,
> keeping it open for the life of the application - each time a function
needs
> to access the DB, it just uses the applications (global) connection that
is
> constantly open and connected.
> This seems to have fixed the problem, however, I am curious, is this an OK
> way to handle the connections - keeping in mind that there are four
separate
> stations - each running the application - at the same time. Therefore, I
> have 4 constantly open connections at the same time.

Connection pooling, the sharing of a single connection among components of
an application, is very common and a good design principle. It is
particularly great for web based/ASP applications, in order to conserve
resource. Persistant connections, keeping a connection open even when not in
use, is something I shy away from in my client server and my web based app
designs. I prefer to create a connection, pool it, and then open and close
it as needed.

It sounds like you are looking for a solution to a symptom, and not your
problem. If I were you, I would investigate the reason your app is timing
out and solve that.

--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com

Connection failed: 18456 error

When one of my users creates an ODBC connection to a SQL database using Windows authentication, he gets this error:

Connection failed: SQLState: '28000' SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
'Domain\username'

The thing is, the 'Domain\username' is not his username and I have no idea where or why it is trying to use that username.

Ideas?

Hi Mike,

You are using integrated security to your sql connection. You would have to use: Server=yourServer;Database=yourDatabase;User Id=somebody;Password=yourpassword;

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||

Hi Mike,

If I read your post correctly, your intention is to use Windows auth, correct? If so, then make sure that you use the "Trusted_Connection=Yes" connection string attibute and remote the username and password attibutes. This will make SQL Server authenticate you based on the credentials under which your client application is running.

Further information regarding the 18456 error can be found here: http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

Il-Sung.

|||

I should have been more clear.

He is not doing any coding. We are just manually creating an ODBC data source system DSN using the ODBC Data Source Administrator.

I was just hoping somebody here had run across situation when creating a data source using Windows NT authentication the connection would fail because it was trying to use another username for some odd reason.

|||

I don't have any problems using the ODBC DSN Administrator and integrated auth. Did you make sure that "With Integrated Windows Authentication" was selected on the "How should SQL Server verify the authenticity of the login ID" prompt?

Il-Sung.

|||Sure did, and this is the only person having this issue. It is just so strange that it is trying to use some other username. I wouldnt' be suprised if not very many people have run into this. Bizzare.|||

This is very strange. I tried connecting using integrated auth with both Sql Native Client and MDAC, and in both cases, if I connect with an invalid account, the error message displays the domain name of the actual user. What is the corresponding 18456 error message in the server's error log?

Il-Sung.

Connection Error 18452 - Login failed for user '(null)'

Hello everybody,
one of our users gets an error message when trying to connect to our SQL
Server database:
Connection failed:
SQLState: '28000'
SQL Server Error 18452
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
'(null)'. Reason: Not associated with a trusted SQL Server connection.
The problem occured suddenly during work. The user can connect to the server
using another computer and other users can connect using his computer.
Knowledge base didn't help so I'm asking you.
Configuration
Client: Windows XP professional (2002/SP1)
ODBC driver: SQL Server (2000.81.9042.00)
Client configuration: TCP/IP
Server: Windows 2000 Server
SQL Server 7.0
Seems the network setup for the user is somehow corrupted, since the message
states the user to be '(null)'.
Any suggestions?
Thanks in advance,
Markus Wolff
This is always an authentication problem somewhere. The null indicates that
user cannot be validated and a null is being passsed to SQL Server.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Rand, what about a solution or al least pointing to one?
"Rand Boyd [MSFT]" wrote:

> This is always an authentication problem somewhere. The null indicates that
> user cannot be validated and a null is being passsed to SQL Server.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>
|||It really depends on where the process is failing. You would
want to check the event logs on the PC having the problems
connecting. Check for any network related issues or
problems. Make sure that PC is contacting the domain
controllers without any problems and that they are
successfully logging into the network.
-Sue
On Mon, 13 Dec 2004 08:25:02 -0800, "JC"
<JC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Rand, what about a solution or al least pointing to one?
>"Rand Boyd [MSFT]" wrote:
|||Login failed for user <null> usually means that on the SQL box, the user
account could not be found in the local security database or in the domain
controller's user database.
For example, on machine1 I log in as machine1\user1. Then I try to log into
SQL on machine2. On machine2 SQL takes the SID of the user and calls
LookupAccountSID API. This attempts to convert the SID to the user name.
LookupAccountSID first looks in local security database on machine2, and
does not find machine1\User1, then looks on domain controller, and still
does not find the SID.
So in general this points to problems with the user account. Perhaps the
user has the same account name defined on their local machine and when they
log in they don't realize that they are logging in as the local User1 versus
the domain User1. Go to the problematic machine and check the user
accounts. If this does not work, perhaps have the domain admin drop the
user account and recreate it.
Matt
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:jd4sr0ds5vna1pc5ph4ri66j5hmri6d51p@.4ax.com...
> It really depends on where the process is failing. You would
> want to check the event logs on the PC having the problems
> connecting. Check for any network related issues or
> problems. Make sure that PC is contacting the domain
> controllers without any problems and that they are
> successfully logging into the network.
> -Sue
> On Mon, 13 Dec 2004 08:25:02 -0800, "JC"
> <JC@.discussions.microsoft.com> wrote:
>

Connection Error 18452 - Login failed for user '(null)'

Hello everybody,
one of our users gets an error message when trying to connect to our SQL
Server database:
Connection failed:
SQLState: '28000'
SQL Server Error 18452
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for
user
'(null)'. Reason: Not associated with a trusted SQL Server connection.
The problem occured suddenly during work. The user can connect to the server
using another computer and other users can connect using his computer.
Knowledge base didn't help so I'm asking you.
Configuration
Client: Windows XP professional (2002/SP1)
ODBC driver: SQL Server (2000.81.9042.00)
Client configuration: TCP/IP
Server: Windows 2000 Server
SQL Server 7.0
Seems the network setup for the user is somehow corrupted, since the message
states the user to be '(null)'.
Any suggestions?
Thanks in advance,
Markus WolffThis is always an authentication problem somewhere. The null indicates that
user cannot be validated and a null is being passsed to SQL Server.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Rand, what about a solution or al least pointing to one?
"Rand Boyd [MSFT]" wrote:

> This is always an authentication problem somewhere. The null indicates tha
t
> user cannot be validated and a null is being passsed to SQL Server.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>|||It really depends on where the process is failing. You would
want to check the event logs on the PC having the problems
connecting. Check for any network related issues or
problems. Make sure that PC is contacting the domain
controllers without any problems and that they are
successfully logging into the network.
-Sue
On Mon, 13 Dec 2004 08:25:02 -0800, "JC"
<JC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Rand, what about a solution or al least pointing to one?
>"Rand Boyd [MSFT]" wrote:
>|||Login failed for user <null> usually means that on the SQL box, the user
account could not be found in the local security database or in the domain
controller's user database.
For example, on machine1 I log in as machine1\user1. Then I try to log into
SQL on machine2. On machine2 SQL takes the SID of the user and calls
LookupAccountSID API. This attempts to convert the SID to the user name.
LookupAccountSID first looks in local security database on machine2, and
does not find machine1\User1, then looks on domain controller, and still
does not find the SID.
So in general this points to problems with the user account. Perhaps the
user has the same account name defined on their local machine and when they
log in they don't realize that they are logging in as the local User1 versus
the domain User1. Go to the problematic machine and check the user
accounts. If this does not work, perhaps have the domain admin drop the
user account and recreate it.
Matt
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:jd4sr0ds5vna1pc5ph4ri66j5hmri6d51p@.
4ax.com...
> It really depends on where the process is failing. You would
> want to check the event logs on the PC having the problems
> connecting. Check for any network related issues or
> problems. Make sure that PC is contacting the domain
> controllers without any problems and that they are
> successfully logging into the network.
> -Sue
> On Mon, 13 Dec 2004 08:25:02 -0800, "JC"
> <JC@.discussions.microsoft.com> wrote:
>
>

Connection Error 18452 - Login failed for user '(null)'

Hello everybody,
one of our users gets an error message when trying to connect to our SQL
Server database:
Connection failed:
SQLState: '28000'
SQL Server Error 18452
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
'(null)'. Reason: Not associated with a trusted SQL Server connection.
The problem occured suddenly during work. The user can connect to the server
using another computer and other users can connect using his computer.
Knowledge base didn't help so I'm asking you.
Configuration
Client: Windows XP professional (2002/SP1)
ODBC driver: SQL Server (2000.81.9042.00)
Client configuration: TCP/IP
Server: Windows 2000 Server
SQL Server 7.0
Seems the network setup for the user is somehow corrupted, since the message
states the user to be '(null)'.
Any suggestions?
Thanks in advance,
Markus Wolff
I have the same problem and this is a problem associated with trusted SQL
Server connections, there is a document out there that says that if you use
named pipes everything is great but using TCP/IP, then you need to configure
Active directory and possibly using kerberos (instead of NTLM). I was hoping
to get more info on how to set out systems at work so that trusted
connections can work.
"Markus Wolff" wrote:

> Hello everybody,
> one of our users gets an error message when trying to connect to our SQL
> Server database:
> Connection failed:
> SQLState: '28000'
> SQL Server Error 18452
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> '(null)'. Reason: Not associated with a trusted SQL Server connection.
> The problem occured suddenly during work. The user can connect to the server
> using another computer and other users can connect using his computer.
> Knowledge base didn't help so I'm asking you.
> Configuration
> Client: Windows XP professional (2002/SP1)
> ODBC driver: SQL Server (2000.81.9042.00)
> Client configuration: TCP/IP
> Server: Windows 2000 Server
> SQL Server 7.0
> Seems the network setup for the user is somehow corrupted, since the message
> states the user to be '(null)'.
> Any suggestions?
> Thanks in advance,
> Markus Wolff
>
>

Connection Error 18452 - Login failed for user '(null)'

Hello everybody,
one of our users gets an error message when trying to connect to our SQL
Server database:
Connection failed:
SQLState: '28000'
SQL Server Error 18452
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for
user
'(null)'. Reason: Not associated with a trusted SQL Server connection.
The problem occured suddenly during work. The user can connect to the server
using another computer and other users can connect using his computer.
Knowledge base didn't help so I'm asking you.
Configuration
Client: Windows XP professional (2002/SP1)
ODBC driver: SQL Server (2000.81.9042.00)
Client configuration: TCP/IP
Server: Windows 2000 Server
SQL Server 7.0
Seems the network setup for the user is somehow corrupted, since the message
states the user to be '(null)'.
Any suggestions?
Thanks in advance,
Markus WolffI have the same problem and this is a problem associated with trusted SQL
Server connections, there is a document out there that says that if you use
named pipes everything is great but using TCP/IP, then you need to configure
Active directory and possibly using kerberos (instead of NTLM). I was hoping
to get more info on how to set out systems at work so that trusted
connections can work.
"Markus Wolff" wrote:

> Hello everybody,
> one of our users gets an error message when trying to connect to our SQL
> Server database:
> Connection failed:
> SQLState: '28000'
> SQL Server Error 18452
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed fo
r user
> '(null)'. Reason: Not associated with a trusted SQL Server connection.
> The problem occured suddenly during work. The user can connect to the serv
er
> using another computer and other users can connect using his computer.
> Knowledge base didn't help so I'm asking you.
> Configuration
> Client: Windows XP professional (2002/SP1)
> ODBC driver: SQL Server (2000.81.9042.00)
> Client configuration: TCP/IP
> Server: Windows 2000 Server
> SQL Server 7.0
> Seems the network setup for the user is somehow corrupted, since the messa
ge
> states the user to be '(null)'.
> Any suggestions?
> Thanks in advance,
> Markus Wolff
>
>

Connecting Trace info to blocked users?

In master.dbo.sysprocesses I can filter for blocked users (Blocked >0)
and I can create a self join(ON SPID = Blocked) to see what user
Loginame is causing the block. In the column [cmd] I can see the
command that the offending blocker is running to cause the block, but
it only says "SELECT" etc. with no details about the stored procedure
that is causing the block.

If I am running a Trace, I can see the exact stored procedures
including the parameters that every cmd is running.

Is there a way to see that same Trace information when looking for
blocked users in master.dbo.sysprocesses, or in some other place?

Ideally what I want is a list of blocked users, who is causing the
blocks and the stored procedure name (or other mischief) causing the
block.

Any help is appreciated.
lqYou can do this, run sp_who2
there is a field named BlkBy grab the id
run DBCC INPUTBUFFER (ID) to get the SQL statement

http://sqlservercode.blogspot.com/|||Is there a way to get the EXEC ('DBCC INPUTBUFFER (BlkBy) WITH
NO_INFOMSGS')) to become a column in the resulting SELECT statement?

CREATE TABLE #SystemUsers (id int identity, SPID int, BlockerLoginame
nvarchar(255), BlockingSQL nvarchar(2000))

INSERT INTO #SystemUsers (SPID, BlockerLoginame, BlockingSQL)
SELECT
SPID,
loginame,
/* (EXEC ('DBCC INPUTBUFFER (BlkBy) WITH NO_INFOMSGS')) As BlockingSQL
??? */
FROM master.dbo.sysprocesses

SELECT
s.spid,
status,
loginame,
hostname,
cmd,
cpu,
last_batch,
login_time,
kpid,
blocked,
x.BlockerLoginame,
waittype,
waittime,
lastwaittype,
waitresource,
dbid,
uid,
memusage,
ecid,
open_tran,
sid,
hostprocess,
nt_domain,
nt_username,
net_address,
net_library,
context_info
FROM master.dbo.sysprocesses s
LEFT OUTER JOIN #SystemUsers x ON x.SPID = s.Blocked
WHERE Blocked >0|||Insert the result of sp_who2 into a table
Delete everything where BlkBy is empty
Loop thru this table and insert the DBCC inputbuffer result into
another table
Join this last table with sysprocesses

http://sqlservercode.blogspot.com/|||This is the part I'm not sure how to write:
"Loop thru this table and insert the DBCC inputbuffer result into
another table"|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> In master.dbo.sysprocesses I can filter for blocked users (Blocked >0)
> and I can create a self join(ON SPID = Blocked) to see what user
> Loginame is causing the block. In the column [cmd] I can see the
> command that the offending blocker is running to cause the block, but
> it only says "SELECT" etc. with no details about the stored procedure
> that is causing the block.
> If I am running a Trace, I can see the exact stored procedures
> including the parameters that every cmd is running.
> Is there a way to see that same Trace information when looking for
> blocked users in master.dbo.sysprocesses, or in some other place?
> Ideally what I want is a list of blocked users, who is causing the
> blocks and the stored procedure name (or other mischief) causing the
> block.

More so, provided that the stored procedures are not encrpyted, you
can get the exact SQL code they are executing. And, no, no need for a
lot of coding. I've already done it for you. :-)

http://www.sommarskog.se/sqlutil/aba_lockinfo.html sounds exactly what
you are looking for.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||laurenq uantrell wrote:
> This is the part I'm not sure how to write:
> "Loop thru this table and insert the DBCC inputbuffer result into
> another table"

I admit I didn't read your post in detail, but I suspect that
fn_get_sql() is what you're looking for - it's more useful than DBCC
INPUTBUFFER. See Books Online for more details; if it's not mentioned in
your current version of BOL then you should get the latest version from
here:

http://www.microsoft.com/sql/techin.../2000/books.asp

But since Erland uses this function in the procedure that he mentioned,
you might well find it easier just to use that.

Simon|||Whew. That's a hefty amount of code, which seems much more complex
than:
"Insert the result of sp_who2 into a table
Delete everything where BlkBy is empty
Loop thru this table and insert the DBCC inputbuffer result into
another table
Join this last table with sysprocesses"

Thanks for that.
Is there a way to force some blocking so that I can test it?|||The next step is to run this every 60 seconds and export the results to
a text file using bcp...
I know that in my original post:

SELECT
SPID,
loginame
FROM master.dbo.sysprocesses
WHERE
BlkBy > 0

will get the ball rolling if a blocking situation exists.

But looking at your aba_lockinfo sproc I'm not sure where to start
something like:

if exists (SELECT * FROM master.dbo.sysprocesses WHERE BlkBy > 0)
begin
/* Blocking exisit so use Erland's aba_lockinfo sproc to BCP to a
text file */
end|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> Whew. That's a hefty amount of code, which seems much more complex
> than:
> "Insert the result of sp_who2 into a table
> Delete everything where BlkBy is empty
> Loop thru this table and insert the DBCC inputbuffer result into
> another table
> Join this last table with sysprocesses"
> Thanks for that.
> Is there a way to force some blocking so that I can test it?

In one window:

BEGIN TRANSACTION
CREATE TABLE #tmp (a int NOT NULL)

In other another

SELECT * FROM tempdb..sysobjects

> The next step is to run this every 60 seconds and export the results to
> a text file using bcp...

BCP with queryout would work in theory, but queryout is known to be
troublesome. The fact the column lengths may very from execution to
execution may also be aggrevate things.

You could also create a table that matches the output from aba_lockinfo,
and then run INSERT EXEC to that table. Again, the dynamic field lengths
could cause some problems, but just make your columns wide enough.

But I would rather just run it from OSQL with output directed to a file.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I didn't realize I'm not running SP3 until trying to run fn_get_sql and
realizing I don't have a column SqlHandle in master.sysprocesses|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> I didn't realize I'm not running SP3 until trying to run fn_get_sql and
> realizing I don't have a column SqlHandle in master.sysprocesses

Since there are other good reasons to upgrade to SP3 - that is Slammer -
I would recommend that you do that. (But there is a pre-SP3 version as
well of aba_lockinfo.)

By the way, I would not recommend that you try to extract pieces of that
code. I mean, it's fairly complex.

(And I will have to rewrite it entirely for SQL 2005...)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,
Yes I saw that and have grabbed the pre-SP3 version. I caused the
interntional blocking with the open transaction as you mentioned, see
the blocking reported in aba_lockinfo, but I don't see reference to the
name of the specific sproc causing the block.|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> Yes I saw that and have grabbed the pre-SP3 version. I caused the
> interntional blocking with the open transaction as you mentioned, see
> the blocking reported in aba_lockinfo, but I don't see reference to the
> name of the specific sproc causing the block.

No, you need to upgrade to SP3 to get that information. Pre-SP3 this
informatin was not available.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yes, I upgraded to SQL Server 2000 SP3a and now it's all clear to me
how this works. Now to execute it as a scheduled process and output it
to a text file on a regular basis...|||uh more importantly.. can't you reccomend upgrading to SP4?

i think that people that don't apply patches should be fired on the spot|||In the real world, sometimes there are reasons why patches are not
applied, but I'm certain we all appreciate your well thought comment,
and those to come.|||In the real world, sometimes there are reasons why patches are not
applied, but I'm certain we all appreciate your well thought comment,
and those to come.

Connecting to SQL server 7 behind ISA server

Hi,
I recently upgrade a WinNT 4 SP6a Proxy server to Win2K SP4 with ISA SP2. I
have to allow remote users to connect to a SQL server 7 SP4 on Winnt 4 SP6a
behind ISA. The IP is static. It was working fine with Proxy but now I have
the following with clients on the Web:
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets] Network error and if I
try SQL Query Analyzer I have
Unable to connect to server xxx.xxx.xxx.xxx
ODBC:Msg 0, Level 16, State 1
[Microsoft][ODBC SQL Server Driver] Time out error
I have the ISA port 1433 and 1344 (for the apps running with SQL) opened and
listenning . If I run netstat -na on both servers, both show ports as
listenning. I can telnet the ISA external IP on both ports. ISA client is
installed on the SQL server.
I ran thru a similar problem when I configured it for the proxy 2 years ago
and don't remember what was the problem exactly. With the proxy, I had to
add wspclnt.ini in the sql server binn folder to map the port 1433 to the
proxy server and added a connection in SQL server network utility which was
mapped to the external IP of the Proxy. I tried it with and without and
there is no difference. I also remember that I had to use credtool on the
SQL server. Do I have to do it with ISA?
I'm familiar with ISA and i often have to open ports for apps and it works
fine so what is diffenrent for SQL server?
Any help would be greatly appreciated.
Thanks in advance.
Steve Amirault
Take the firewall client off the SQL server and make sure the default
gateway of the SQL server is the ISA server, if that is the route to the
internet. Did you make the Server Publishing Rule for the machine?
Scott Harding
MCSE, MCSA, A+, Network+
Microsoft MVP - Windows NT Server
"Steve" <as@.joe.ca> wrote in message
news:uqKNAmSsEHA.2660@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I recently upgrade a WinNT 4 SP6a Proxy server to Win2K SP4 with ISA SP2.
I
> have to allow remote users to connect to a SQL server 7 SP4 on Winnt 4
SP6a
> behind ISA. The IP is static. It was working fine with Proxy but now I
have
> the following with clients on the Web:
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets] Network error and if I
> try SQL Query Analyzer I have
> Unable to connect to server xxx.xxx.xxx.xxx
> ODBC:Msg 0, Level 16, State 1
> [Microsoft][ODBC SQL Server Driver] Time out error
> I have the ISA port 1433 and 1344 (for the apps running with SQL) opened
and
> listenning . If I run netstat -na on both servers, both show ports as
> listenning. I can telnet the ISA external IP on both ports. ISA client is
> installed on the SQL server.
> I ran thru a similar problem when I configured it for the proxy 2 years
ago
> and don't remember what was the problem exactly. With the proxy, I had to
> add wspclnt.ini in the sql server binn folder to map the port 1433 to the
> proxy server and added a connection in SQL server network utility which
was
> mapped to the external IP of the Proxy. I tried it with and without and
> there is no difference. I also remember that I had to use credtool on the
> SQL server. Do I have to do it with ISA?
> I'm familiar with ISA and i often have to open ports for apps and it works
> fine so what is diffenrent for SQL server?
> Any help would be greatly appreciated.
> Thanks in advance.
> Steve Amirault
>
|||Yes there is a Server rule. Guess what, by doing it the same way I did it
with the former proxy, it works. I disabled the server publishing rule,
added the wspcfg.ini file in the mssql7\binn folder and it works? At the
setup time and according to the situations I had with the ISA, I first
thought that it wouldn't work using the same settings as it was with the
proxy server but it appears that is not the case.
Thanks anyway.
Steve
"Scott Harding - MS MVP" <scrockel@.**NO_SPAM**hotmail.com> a crit dans le
message de news:OJeoP1TsEHA.3200@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Take the firewall client off the SQL server and make sure the default
> gateway of the SQL server is the ISA server, if that is the route to the
> internet. Did you make the Server Publishing Rule for the machine?
> --
> Scott Harding
> MCSE, MCSA, A+, Network+
> Microsoft MVP - Windows NT Server
> "Steve" <as@.joe.ca> wrote in message
> news:uqKNAmSsEHA.2660@.TK2MSFTNGP12.phx.gbl...
SP2.[vbcol=seagreen]
> I
> SP6a
> have
I[vbcol=seagreen]
> and
is[vbcol=seagreen]
> ago
to[vbcol=seagreen]
the[vbcol=seagreen]
> was
the[vbcol=seagreen]
works
>
|||Interesting...you shouldn't have to do that but it depends I think if the
SQL server is a Secure NAT client or not.
Scott Harding
MCSE, MCSA, A+, Network+
Microsoft MVP - Windows NT Server
"Steve" <as@.joe.ca> wrote in message
news:uplh6uUsEHA.3412@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Yes there is a Server rule. Guess what, by doing it the same way I did it
> with the former proxy, it works. I disabled the server publishing rule,
> added the wspcfg.ini file in the mssql7\binn folder and it works? At the
> setup time and according to the situations I had with the ISA, I first
> thought that it wouldn't work using the same settings as it was with the
> proxy server but it appears that is not the case.
> Thanks anyway.
> Steve
>
> "Scott Harding - MS MVP" <scrockel@.**NO_SPAM**hotmail.com> a crit dans le
> message de news:OJeoP1TsEHA.3200@.TK2MSFTNGP09.phx.gbl...
> SP2.
4[vbcol=seagreen]
if[vbcol=seagreen]
> I
opened[vbcol=seagreen]
> is
years[vbcol=seagreen]
> to
> the
which[vbcol=seagreen]
and
> the
> works
>

Connecting to SQL server 7 behind ISA server

Hi,
I recently upgrade a WinNT 4 SP6a Proxy server to Win2K SP4 with ISA SP2. I
have to allow remote users to connect to a SQL server 7 SP4 on Winnt 4 SP6a
behind ISA. The IP is static. It was working fine with Proxy but now I have
the following with clients on the Web:
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets] Network erro
r and if I
try SQL Query Analyzer I have
Unable to connect to server xxx.xxx.xxx.xxx
ODBC:Msg 0, Level 16, State 1
[Microsoft][ODBC SQL Server Driver] Time out error
I have the ISA port 1433 and 1344 (for the apps running with SQL) opened and
listenning . If I run netstat -na on both servers, both show ports as
listenning. I can telnet the ISA external IP on both ports. ISA client is
installed on the SQL server.
I ran thru a similar problem when I configured it for the proxy 2 years ago
and don't remember what was the problem exactly. With the proxy, I had to
add wspclnt.ini in the sql server binn folder to map the port 1433 to the
proxy server and added a connection in SQL server network utility which was
mapped to the external IP of the Proxy. I tried it with and without and
there is no difference. I also remember that I had to use credtool on the
SQL server. Do I have to do it with ISA?
I'm familiar with ISA and i often have to open ports for apps and it works
fine so what is diffenrent for SQL server?
Any help would be greatly appreciated.
Thanks in advance.
Steve AmiraultTake the firewall client off the SQL server and make sure the default
gateway of the SQL server is the ISA server, if that is the route to the
internet. Did you make the Server Publishing Rule for the machine?
Scott Harding
MCSE, MCSA, A+, Network+
Microsoft MVP - Windows NT Server
"Steve" <as@.joe.ca> wrote in message
news:uqKNAmSsEHA.2660@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I recently upgrade a WinNT 4 SP6a Proxy server to Win2K SP4 with ISA SP2.
I
> have to allow remote users to connect to a SQL server 7 SP4 on Winnt 4
SP6a
> behind ISA. The IP is static. It was working fine with Proxy but now I
have
> the following with clients on the Web:
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets] Network er
ror and if I
> try SQL Query Analyzer I have
> Unable to connect to server xxx.xxx.xxx.xxx
> ODBC:Msg 0, Level 16, State 1
> [Microsoft][ODBC SQL Server Driver] Time out error
> I have the ISA port 1433 and 1344 (for the apps running with SQL) opened
and
> listenning . If I run netstat -na on both servers, both show ports as
> listenning. I can telnet the ISA external IP on both ports. ISA client is
> installed on the SQL server.
> I ran thru a similar problem when I configured it for the proxy 2 years
ago
> and don't remember what was the problem exactly. With the proxy, I had to
> add wspclnt.ini in the sql server binn folder to map the port 1433 to the
> proxy server and added a connection in SQL server network utility which
was
> mapped to the external IP of the Proxy. I tried it with and without and
> there is no difference. I also remember that I had to use credtool on the
> SQL server. Do I have to do it with ISA?
> I'm familiar with ISA and i often have to open ports for apps and it works
> fine so what is diffenrent for SQL server?
> Any help would be greatly appreciated.
> Thanks in advance.
> Steve Amirault
>|||Yes there is a Server rule. Guess what, by doing it the same way I did it
with the former proxy, it works. I disabled the server publishing rule,
added the wspcfg.ini file in the mssql7\binn folder and it works? At the
setup time and according to the situations I had with the ISA, I first
thought that it wouldn't work using the same settings as it was with the
proxy server but it appears that is not the case.
Thanks anyway.
Steve
"Scott Harding - MS MVP" <scrockel@.**NO_SPAM**hotmail.com> a crit dans le
message de news:OJeoP1TsEHA.3200@.TK2MSFTNGP09.phx.gbl...
> Take the firewall client off the SQL server and make sure the default
> gateway of the SQL server is the ISA server, if that is the route to the
> internet. Did you make the Server Publishing Rule for the machine?
> --
> Scott Harding
> MCSE, MCSA, A+, Network+
> Microsoft MVP - Windows NT Server
> "Steve" <as@.joe.ca> wrote in message
> news:uqKNAmSsEHA.2660@.TK2MSFTNGP12.phx.gbl...
SP2.[vbcol=seagreen]
> I
> SP6a
> have
I[vbcol=seagreen]
> and
is[vbcol=seagreen]
> ago
to[vbcol=seagreen]
the[vbcol=seagreen]
> was
the[vbcol=seagreen]
works[vbcol=seagreen]
>|||Interesting...you shouldn't have to do that but it depends I think if the
SQL server is a Secure NAT client or not.
Scott Harding
MCSE, MCSA, A+, Network+
Microsoft MVP - Windows NT Server
"Steve" <as@.joe.ca> wrote in message
news:uplh6uUsEHA.3412@.TK2MSFTNGP14.phx.gbl...
> Yes there is a Server rule. Guess what, by doing it the same way I did it
> with the former proxy, it works. I disabled the server publishing rule,
> added the wspcfg.ini file in the mssql7\binn folder and it works? At the
> setup time and according to the situations I had with the ISA, I first
> thought that it wouldn't work using the same settings as it was with the
> proxy server but it appears that is not the case.
> Thanks anyway.
> Steve
>
> "Scott Harding - MS MVP" <scrockel@.**NO_SPAM**hotmail.com> a crit dans le
> message de news:OJeoP1TsEHA.3200@.TK2MSFTNGP09.phx.gbl...
> SP2.
4[vbcol=seagreen]
if[vbcol=seagreen]
> I
opened[vbcol=seagreen]
> is
years[vbcol=seagreen]
> to
> the
which[vbcol=seagreen]
and[vbcol=seagreen]
> the
> works
>