Hi,
I'm struggling trying to catch the current user in MSSQL with
SYSTEM_USER through an Intranet Application. I try to do this to create
audit trail of changes to data. (I'm aware of the performance penalties
it causes due to application pooling problems with integrated
security.)
Now to my problem:
Using IIS6.0 ASP.NET 1.1 on Windows2003 with Windows Authentication
enabled and Anonymous logon disabled. I get the correct user and can
verify him/her in IIS. Now I want this identity to be used when logging
into SQLServer. To keep things simple, lets say the SQLServer is on the
same machine (it is right now, but won't be further on). The aspx-pages
use a wrapper to call COM-dll's that connect to the database using an
ODBC file DSN. I have set <indentity impersonate="true" /> in
Web.Config and have created the Windows Integrated Security login
accounts in SQLServer with proper db-roles to update the data.
Unfortunately I keep getting stuck with the NT AUTHORISATION/NETWORK
SERVICE account when the connection reaches SQLServer. It was working
last week, but now I've made a clean install and just can't get it to
work.
Please help with any ideas! The <identity impersonate="true" /> isn't
listening to me.
/RichardIt looks like you might be missing setting up delegation.
The following articles should get you started:
How To: Implement Kerberos Delegation for Windows 2000
http://msdn.microsoft.com/library/d.../>
NetHT05.asp
How to use Kerberos authentication in SQL Server
http://support.microsoft.com/?id=319723
-Sue
On 15 May 2006 17:23:09 -0700, richard.allgardh@.medinit.se
wrote:
>Hi,
>I'm struggling trying to catch the current user in MSSQL with
>SYSTEM_USER through an Intranet Application. I try to do this to create
>audit trail of changes to data. (I'm aware of the performance penalties
>it causes due to application pooling problems with integrated
>security.)
>Now to my problem:
>Using IIS6.0 ASP.NET 1.1 on Windows2003 with Windows Authentication
>enabled and Anonymous logon disabled. I get the correct user and can
>verify him/her in IIS. Now I want this identity to be used when logging
>into SQLServer. To keep things simple, lets say the SQLServer is on the
>same machine (it is right now, but won't be further on). The aspx-pages
>use a wrapper to call COM-dll's that connect to the database using an
>ODBC file DSN. I have set <indentity impersonate="true" /> in
>Web.Config and have created the Windows Integrated Security login
>accounts in SQLServer with proper db-roles to update the data.
>Unfortunately I keep getting stuck with the NT AUTHORISATION/NETWORK
>SERVICE account when the connection reaches SQLServer. It was working
>last week, but now I've made a clean install and just can't get it to
>work.
>Please help with any ideas! The <identity impersonate="true" /> isn't
>listening to me.
>/Richard
Showing posts with label identity. Show all posts
Showing posts with label identity. Show all posts
Monday, March 19, 2012
Monday, March 12, 2012
Integer or uniqueidentifier is better
can you please give me an idea about the advantages/disadvantages between
using numeric identity versus uniquidentifier in sql 2005 and which is a
better choice generally?
Is there an article on MSDN in a table format that lists the differences
plus recommandations on when to use each one of them?
thanksSalamElias,
Uniqueidentifiers have one distinct advantage: they are unique across
platforms and multiple servers on any network (part of the uniqueidentifier
is based upon the network card mac address). The application can generate
the id and work with it before any trip is made to the DB. It doesn't have
to wait for the round trip to the DB. Because of the guaranteed uniqueness,
it facilitates distributed processing on multiple database servers without
causing PK violation issues if consolidating to a central server.
Disadvantages: The uniqueidentifier is not sequential by nature. This will
cause fragmentation of clustered indexes as records are inserted and
affected tables grow, requiring a re-indexing process to be included as part
of a normal maintenance strategy. Also, it takes up 16 bytes instead of 4
associated with the integer or 8 associated with the bigint datatypes. This
will have some impact on join performance, but it probably will not be that
significant.
All that said, I'm a fan of uniqueidentifiers.
-- Bill
"SalamElias" <eliassal@.online.nospam> wrote in message
news:16C42B55-FE46-439D-8773-F9112FE36D8E@.microsoft.com...
> can you please give me an idea about the advantages/disadvantages between
> using numeric identity versus uniquidentifier in sql 2005 and which is a
> better choice generally?
> Is there an article on MSDN in a table format that lists the differences
> plus recommandations on when to use each one of them?
> thanks|||SalamElias,
Everything that Bill says is spot-on!
All that said, I really dislike uniqueidentifiers. If you don't need
them (and don't use them), you won't miss any of their advantages. If
you do use them, you will notice their disadvantages.
HTH,
Gert-Jan
AlterEgo wrote:
> SalamElias,
> Uniqueidentifiers have one distinct advantage: they are unique across
> platforms and multiple servers on any network (part of the uniqueidentifier
> is based upon the network card mac address). The application can generate
> the id and work with it before any trip is made to the DB. It doesn't have
> to wait for the round trip to the DB. Because of the guaranteed uniqueness,
> it facilitates distributed processing on multiple database servers without
> causing PK violation issues if consolidating to a central server.
> Disadvantages: The uniqueidentifier is not sequential by nature. This will
> cause fragmentation of clustered indexes as records are inserted and
> affected tables grow, requiring a re-indexing process to be included as part
> of a normal maintenance strategy. Also, it takes up 16 bytes instead of 4
> associated with the integer or 8 associated with the bigint datatypes. This
> will have some impact on join performance, but it probably will not be that
> significant.
> All that said, I'm a fan of uniqueidentifiers.
> -- Bill
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:16C42B55-FE46-439D-8773-F9112FE36D8E@.microsoft.com...
> > can you please give me an idea about the advantages/disadvantages between
> > using numeric identity versus uniquidentifier in sql 2005 and which is a
> > better choice generally?
> > Is there an article on MSDN in a table format that lists the differences
> > plus recommandations on when to use each one of them?
> > thanks
using numeric identity versus uniquidentifier in sql 2005 and which is a
better choice generally?
Is there an article on MSDN in a table format that lists the differences
plus recommandations on when to use each one of them?
thanksSalamElias,
Uniqueidentifiers have one distinct advantage: they are unique across
platforms and multiple servers on any network (part of the uniqueidentifier
is based upon the network card mac address). The application can generate
the id and work with it before any trip is made to the DB. It doesn't have
to wait for the round trip to the DB. Because of the guaranteed uniqueness,
it facilitates distributed processing on multiple database servers without
causing PK violation issues if consolidating to a central server.
Disadvantages: The uniqueidentifier is not sequential by nature. This will
cause fragmentation of clustered indexes as records are inserted and
affected tables grow, requiring a re-indexing process to be included as part
of a normal maintenance strategy. Also, it takes up 16 bytes instead of 4
associated with the integer or 8 associated with the bigint datatypes. This
will have some impact on join performance, but it probably will not be that
significant.
All that said, I'm a fan of uniqueidentifiers.
-- Bill
"SalamElias" <eliassal@.online.nospam> wrote in message
news:16C42B55-FE46-439D-8773-F9112FE36D8E@.microsoft.com...
> can you please give me an idea about the advantages/disadvantages between
> using numeric identity versus uniquidentifier in sql 2005 and which is a
> better choice generally?
> Is there an article on MSDN in a table format that lists the differences
> plus recommandations on when to use each one of them?
> thanks|||SalamElias,
Everything that Bill says is spot-on!
All that said, I really dislike uniqueidentifiers. If you don't need
them (and don't use them), you won't miss any of their advantages. If
you do use them, you will notice their disadvantages.
HTH,
Gert-Jan
AlterEgo wrote:
> SalamElias,
> Uniqueidentifiers have one distinct advantage: they are unique across
> platforms and multiple servers on any network (part of the uniqueidentifier
> is based upon the network card mac address). The application can generate
> the id and work with it before any trip is made to the DB. It doesn't have
> to wait for the round trip to the DB. Because of the guaranteed uniqueness,
> it facilitates distributed processing on multiple database servers without
> causing PK violation issues if consolidating to a central server.
> Disadvantages: The uniqueidentifier is not sequential by nature. This will
> cause fragmentation of clustered indexes as records are inserted and
> affected tables grow, requiring a re-indexing process to be included as part
> of a normal maintenance strategy. Also, it takes up 16 bytes instead of 4
> associated with the integer or 8 associated with the bigint datatypes. This
> will have some impact on join performance, but it probably will not be that
> significant.
> All that said, I'm a fan of uniqueidentifiers.
> -- Bill
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:16C42B55-FE46-439D-8773-F9112FE36D8E@.microsoft.com...
> > can you please give me an idea about the advantages/disadvantages between
> > using numeric identity versus uniquidentifier in sql 2005 and which is a
> > better choice generally?
> > Is there an article on MSDN in a table format that lists the differences
> > plus recommandations on when to use each one of them?
> > thanks
Integer or uniqueidentifier is better
can you please give me an idea about the advantages/disadvantages between
using numeric identity versus uniquidentifier in sql 2005 and which is a
better choice generally?
Is there an article on MSDN in a table format that lists the differences
plus recommandations on when to use each one of them?
thanksSalamElias,
Uniqueidentifiers have one distinct advantage: they are unique across
platforms and multiple servers on any network (part of the uniqueidentifier
is based upon the network card mac address). The application can generate
the id and work with it before any trip is made to the DB. It doesn't have
to wait for the round trip to the DB. Because of the guaranteed uniqueness,
it facilitates distributed processing on multiple database servers without
causing PK violation issues if consolidating to a central server.
Disadvantages: The uniqueidentifier is not sequential by nature. This will
cause fragmentation of clustered indexes as records are inserted and
affected tables grow, requiring a re-indexing process to be included as part
of a normal maintenance strategy. Also, it takes up 16 bytes instead of 4
associated with the integer or 8 associated with the bigint datatypes. This
will have some impact on join performance, but it probably will not be that
significant.
All that said, I'm a fan of uniqueidentifiers.
-- Bill
"SalamElias" <eliassal@.online.nospam> wrote in message
news:16C42B55-FE46-439D-8773-F9112FE36D8E@.microsoft.com...
> can you please give me an idea about the advantages/disadvantages between
> using numeric identity versus uniquidentifier in sql 2005 and which is a
> better choice generally?
> Is there an article on MSDN in a table format that lists the differences
> plus recommandations on when to use each one of them?
> thanks|||SalamElias,
Everything that Bill says is spot-on!
All that said, I really dislike uniqueidentifiers. If you don't need
them (and don't use them), you won't miss any of their advantages. If
you do use them, you will notice their disadvantages.
HTH,
Gert-Jan
AlterEgo wrote:[vbcol=seagreen]
> SalamElias,
> Uniqueidentifiers have one distinct advantage: they are unique across
> platforms and multiple servers on any network (part of the uniqueidentifie
r
> is based upon the network card mac address). The application can generate
> the id and work with it before any trip is made to the DB. It doesn't have
> to wait for the round trip to the DB. Because of the guaranteed uniqueness
,
> it facilitates distributed processing on multiple database servers without
> causing PK violation issues if consolidating to a central server.
> Disadvantages: The uniqueidentifier is not sequential by nature. This will
> cause fragmentation of clustered indexes as records are inserted and
> affected tables grow, requiring a re-indexing process to be included as pa
rt
> of a normal maintenance strategy. Also, it takes up 16 bytes instead of 4
> associated with the integer or 8 associated with the bigint datatypes. Thi
s
> will have some impact on join performance, but it probably will not be tha
t
> significant.
> All that said, I'm a fan of uniqueidentifiers.
> -- Bill
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:16C42B55-FE46-439D-8773-F9112FE36D8E@.microsoft.com...
using numeric identity versus uniquidentifier in sql 2005 and which is a
better choice generally?
Is there an article on MSDN in a table format that lists the differences
plus recommandations on when to use each one of them?
thanksSalamElias,
Uniqueidentifiers have one distinct advantage: they are unique across
platforms and multiple servers on any network (part of the uniqueidentifier
is based upon the network card mac address). The application can generate
the id and work with it before any trip is made to the DB. It doesn't have
to wait for the round trip to the DB. Because of the guaranteed uniqueness,
it facilitates distributed processing on multiple database servers without
causing PK violation issues if consolidating to a central server.
Disadvantages: The uniqueidentifier is not sequential by nature. This will
cause fragmentation of clustered indexes as records are inserted and
affected tables grow, requiring a re-indexing process to be included as part
of a normal maintenance strategy. Also, it takes up 16 bytes instead of 4
associated with the integer or 8 associated with the bigint datatypes. This
will have some impact on join performance, but it probably will not be that
significant.
All that said, I'm a fan of uniqueidentifiers.
-- Bill
"SalamElias" <eliassal@.online.nospam> wrote in message
news:16C42B55-FE46-439D-8773-F9112FE36D8E@.microsoft.com...
> can you please give me an idea about the advantages/disadvantages between
> using numeric identity versus uniquidentifier in sql 2005 and which is a
> better choice generally?
> Is there an article on MSDN in a table format that lists the differences
> plus recommandations on when to use each one of them?
> thanks|||SalamElias,
Everything that Bill says is spot-on!
All that said, I really dislike uniqueidentifiers. If you don't need
them (and don't use them), you won't miss any of their advantages. If
you do use them, you will notice their disadvantages.
HTH,
Gert-Jan
AlterEgo wrote:[vbcol=seagreen]
> SalamElias,
> Uniqueidentifiers have one distinct advantage: they are unique across
> platforms and multiple servers on any network (part of the uniqueidentifie
r
> is based upon the network card mac address). The application can generate
> the id and work with it before any trip is made to the DB. It doesn't have
> to wait for the round trip to the DB. Because of the guaranteed uniqueness
,
> it facilitates distributed processing on multiple database servers without
> causing PK violation issues if consolidating to a central server.
> Disadvantages: The uniqueidentifier is not sequential by nature. This will
> cause fragmentation of clustered indexes as records are inserted and
> affected tables grow, requiring a re-indexing process to be included as pa
rt
> of a normal maintenance strategy. Also, it takes up 16 bytes instead of 4
> associated with the integer or 8 associated with the bigint datatypes. Thi
s
> will have some impact on join performance, but it probably will not be tha
t
> significant.
> All that said, I'm a fan of uniqueidentifiers.
> -- Bill
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:16C42B55-FE46-439D-8773-F9112FE36D8E@.microsoft.com...
Friday, March 9, 2012
Integer or uniqueidentifier is better
can you please give me an idea about the advantages/disadvantages between
using numeric identity versus uniquidentifier in sql 2005 and which is a
better choice generally?
Is there an article on MSDN in a table format that lists the differences
plus recommandations on when to use each one of them?
thanks
SalamElias,
Uniqueidentifiers have one distinct advantage: they are unique across
platforms and multiple servers on any network (part of the uniqueidentifier
is based upon the network card mac address). The application can generate
the id and work with it before any trip is made to the DB. It doesn't have
to wait for the round trip to the DB. Because of the guaranteed uniqueness,
it facilitates distributed processing on multiple database servers without
causing PK violation issues if consolidating to a central server.
Disadvantages: The uniqueidentifier is not sequential by nature. This will
cause fragmentation of clustered indexes as records are inserted and
affected tables grow, requiring a re-indexing process to be included as part
of a normal maintenance strategy. Also, it takes up 16 bytes instead of 4
associated with the integer or 8 associated with the bigint datatypes. This
will have some impact on join performance, but it probably will not be that
significant.
All that said, I'm a fan of uniqueidentifiers.
-- Bill
"SalamElias" <eliassal@.online.nospam> wrote in message
news:16C42B55-FE46-439D-8773-F9112FE36D8E@.microsoft.com...
> can you please give me an idea about the advantages/disadvantages between
> using numeric identity versus uniquidentifier in sql 2005 and which is a
> better choice generally?
> Is there an article on MSDN in a table format that lists the differences
> plus recommandations on when to use each one of them?
> thanks
using numeric identity versus uniquidentifier in sql 2005 and which is a
better choice generally?
Is there an article on MSDN in a table format that lists the differences
plus recommandations on when to use each one of them?
thanks
SalamElias,
Uniqueidentifiers have one distinct advantage: they are unique across
platforms and multiple servers on any network (part of the uniqueidentifier
is based upon the network card mac address). The application can generate
the id and work with it before any trip is made to the DB. It doesn't have
to wait for the round trip to the DB. Because of the guaranteed uniqueness,
it facilitates distributed processing on multiple database servers without
causing PK violation issues if consolidating to a central server.
Disadvantages: The uniqueidentifier is not sequential by nature. This will
cause fragmentation of clustered indexes as records are inserted and
affected tables grow, requiring a re-indexing process to be included as part
of a normal maintenance strategy. Also, it takes up 16 bytes instead of 4
associated with the integer or 8 associated with the bigint datatypes. This
will have some impact on join performance, but it probably will not be that
significant.
All that said, I'm a fan of uniqueidentifiers.
-- Bill
"SalamElias" <eliassal@.online.nospam> wrote in message
news:16C42B55-FE46-439D-8773-F9112FE36D8E@.microsoft.com...
> can you please give me an idea about the advantages/disadvantages between
> using numeric identity versus uniquidentifier in sql 2005 and which is a
> better choice generally?
> Is there an article on MSDN in a table format that lists the differences
> plus recommandations on when to use each one of them?
> thanks
Integer identity value handling in export / import
Hi,
I have a question regarding export / import when you have tables with autonumber (identity) columns as primary surrogate keys. Does export / import retiain the values of these columns so that referential integriy is not broken?
CharlesYes!
Depending on how you import the data, make sure you choose the correct option that you wish to explicitely insert your "own values" into an IDENTITY column.
You might want to check BOL for SET IDENTITY_INSERT or BULK INSERT...KEEPIDENTITY or BCP -E. There is also some equivalent when you use DTS, but since I don't use DTS, I don't know what option to check there.
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)|||
I am Hitesh.
I have the same issue. While exporting/importing data (from SQL 2005 to SQL 2005) IDENTITY column values get altered..this way my referencial integrity gets broken that is not good at all....I am familier that using COPY WIZARD & Backup/Restore this can be avoided but I want to know ONLY for DTS import/export wizard....Will you please help me....it's very very very urgent to me.
Thanks.
Integer identity value handling in export / import
Hi,
I have a question regarding export / import when you have tables with autonumber (identity) columns as primary surrogate keys. Does export / import retiain the values of these columns so that referential integriy is not broken?
CharlesYes!
Depending on how you import the data, make sure you choose the correct option that you wish to explicitely insert your "own values" into an IDENTITY column.
You might want to check BOL for SET IDENTITY_INSERT or BULK INSERT...KEEPIDENTITY or BCP -E. There is also some equivalent when you use DTS, but since I don't use DTS, I don't know what option to check there.
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)
|||
I am Hitesh.
I have the same issue. While exporting/importing data (from SQL 2005 to SQL 2005) IDENTITY column values get altered..this way my referencial integrity gets broken that is not good at all....I am familier that using COPY WIZARD & Backup/Restore this can be avoided but I want to know ONLY for DTS import/export wizard....Will you please help me....it's very very very urgent to me.
Thanks.
Subscribe to:
Posts (Atom)