What is Regular SQL Index? Where can I read more about the subject? Or how do can I use it in my tabels?not sure I have heard this one before UNLESS "Regular SQL Index" refers to a NON-Clustered index. Where did you hear about it?|||Originally posted by Paul Young
not sure I have heard this one before UNLESS "Regular SQL Index" refers to a NON-Clustered index. Where did you hear about it?
Here http://www.databasejournal.com/features/mssql/article.php/1438211#part_3|||The article was talking about Full-text search indexes as compared to indexes on a column or columns.
Showing posts with label regular. Show all posts
Showing posts with label regular. Show all posts
Friday, March 9, 2012
regular primary keys vs autonumber primary keys
What is the best way to handle primary key selection in general? It would seem that autonumbered primary keys would result in faster query times, but what if there is another field in the table which is a PK canidate and must be unique? For example lets say I have a product table with an autonumbered PK. This table also has a product ID which must be unique. Would it be best to keep the autonumbered ID as the PK and put a unique constraint on the product ID maybe? OR would it be best to change the PK to the product ID and nix the autonumber all-together? All thoughts on this would be appreciated.Originally posted by Chuckt
What is the best way to handle primary key selection in general? It would seem that autonumbered primary keys would result in faster query times, but what if there is another field in the table which is a PK canidate and must be unique? For example lets say I have a product table with an autonumbered PK. This table also has a product ID which must be unique. Would it be best to keep the autonumbered ID as the PK and put a unique constraint on the product ID maybe? OR would it be best to change the PK to the product ID and nix the autonumber all-together? All thoughts on this would be appreciated.
If table does not have natural key like state abbreviation (KY - Kentucky - I was living there almost three years) you have to create fake primary key. Usually it is integer (SQL2000 does have bigint) field. Should you use IDENTITY? It depends on what kind of table (lookup or not), what kind of database, will you use replication or not, etc. Identity does have some pluses and exactly the same quantity of minuses. If you are going to change data frequently in tables by 'hands' (not from application) - do not use IDENTITY. You can use SET IDENTITY_INSERT for inserting into the identity column of a table.|||Thanks for the reply snail. My dilemma is this... I have a table that relates two other tables(its primary key is the primary keys of the other two tables it is relating). It has over 2 million rows. The primary keys of all three tables are clustered indexes and the data types of the fields are varchar 20. The query times are a little slow and I was trying to figure out ways to increase the speed. Would it be faster to use an autonumber for the three tables? It would seem to me that it would be faster to join in a query based on an index comprised of less characters. How much faster and if it is worth it I am not sure.|||Couple things to think about.
(1) Joins are better utilized on integer fields and are faster.
(2) What type of inserts do you have, the nice thing about having an identity as a PK and clustered index is all inserts fall to the last leaf level thereby reducing page splits. If your data is solely for querying data, this is a waste though.
(3) Are you running certain queries the majority of the time that only consist of a few of the columns? If so, you might think about a composite non-clustered index to have applicable columns in. Having a non-clustered covering index on the columns you need is faster than having to conduct table scans, or even clustered index key locks.
HTH|||"(2) What type of inserts do you have, the nice thing about having an identity as a PK and clustered index is all inserts fall to the last leaf level thereby reducing page splits."
Yes, but unless you reindex won't your query efficiency be reduced because the average number of pages traversed will increase. In effect, the data structure will be lopsided toward the latest inserted values:
/\
../\
.../\
...../\
....../\
instead of:
../\
./\/\
/\/\/\
blindman|||An int as a foreign key will be faster than a wider key because it takes up less space and less page reads.
It can cause the system to be slower though because to obtain any data from the other table you will need to join to it. Using the natural key the data in that key is available on the referred table.
Not so bad with just two tables but
t1 (col1, col2, col3)
t2 (t1_col1, t1_co2,t1_col3)
t3 (t1_col3)
This is quite common. With the natural keys you can join t2 and t3. If you replace with artificial keys you would have to join to t1 as well so causing more page reads and using up more memory.
It's a subject that people get very passionate about. I disagree with those that say that all tables should have an artificial key and this should always be used for the join field but wouldn't disagree that it can sometimes be useful to create one for efficiency or ease of coding.|||well, that's why after going through structural normalization process designers revise the design with DE-normalization steps. it still is an evidence of consistency and is easy to follow. btw, you don't have to join t1 in the example you give while using "artificial keys".|||The B-tree still maintains ranges but yes it is a trade-off, if you are heavy reads, low inserts, you should not use this method, but if you are heavy inserts with medium to heavy reads, this is good for minimizing page splits without having to play with fill factors.
Nigel, I'm one of those that recommends a surrogate key on every table, just seen too many times a company changes their primary key algorithm and it makes things easier in my opinion when you push to a warehouse. But I won't argue that passionately for it, to each his own :)
Originally posted by blindman
"(2) What type of inserts do you have, the nice thing about having an identity as a PK and clustered index is all inserts fall to the last leaf level thereby reducing page splits."
Yes, but unless you reindex won't your query efficiency be reduced because the average number of pages traversed will increase. In effect, the data structure will be lopsided toward the latest inserted values:
/\
../\
.../\
...../\
....../\
instead of:
../\
./\/\
/\/\/\
blindman|||I used to use natural keys a lot, but I found that the lower-level tables in schemas designed with natural keys tended to have multi-column indexes with multiple joins to related tables. I still consider using natural keys for higher-level tables, especially look-up tables.
Consider the needs of your application as well. By standardizing the key you simplify things for developers who always know what type of field to submit for searches, etc. If you use a natural key and then decide you want to change it (length or type) you may force changes in your middle tier.
...and one other cool thing I was able to do with GUIDs. We had developers who wanted to be able to lookup information based on the ID of an employee record, but they also wanted the same functionality by submitting the session-specific security token the employee was using. Since both were GUIDs, I was able to create a single procedure which required only a single parameter @.EmployeeIDorToken, and determine within the procedure which one was submitted. I could count on the fact that the same GUID would never exist in both tables.
blindman
What is the best way to handle primary key selection in general? It would seem that autonumbered primary keys would result in faster query times, but what if there is another field in the table which is a PK canidate and must be unique? For example lets say I have a product table with an autonumbered PK. This table also has a product ID which must be unique. Would it be best to keep the autonumbered ID as the PK and put a unique constraint on the product ID maybe? OR would it be best to change the PK to the product ID and nix the autonumber all-together? All thoughts on this would be appreciated.
If table does not have natural key like state abbreviation (KY - Kentucky - I was living there almost three years) you have to create fake primary key. Usually it is integer (SQL2000 does have bigint) field. Should you use IDENTITY? It depends on what kind of table (lookup or not), what kind of database, will you use replication or not, etc. Identity does have some pluses and exactly the same quantity of minuses. If you are going to change data frequently in tables by 'hands' (not from application) - do not use IDENTITY. You can use SET IDENTITY_INSERT for inserting into the identity column of a table.|||Thanks for the reply snail. My dilemma is this... I have a table that relates two other tables(its primary key is the primary keys of the other two tables it is relating). It has over 2 million rows. The primary keys of all three tables are clustered indexes and the data types of the fields are varchar 20. The query times are a little slow and I was trying to figure out ways to increase the speed. Would it be faster to use an autonumber for the three tables? It would seem to me that it would be faster to join in a query based on an index comprised of less characters. How much faster and if it is worth it I am not sure.|||Couple things to think about.
(1) Joins are better utilized on integer fields and are faster.
(2) What type of inserts do you have, the nice thing about having an identity as a PK and clustered index is all inserts fall to the last leaf level thereby reducing page splits. If your data is solely for querying data, this is a waste though.
(3) Are you running certain queries the majority of the time that only consist of a few of the columns? If so, you might think about a composite non-clustered index to have applicable columns in. Having a non-clustered covering index on the columns you need is faster than having to conduct table scans, or even clustered index key locks.
HTH|||"(2) What type of inserts do you have, the nice thing about having an identity as a PK and clustered index is all inserts fall to the last leaf level thereby reducing page splits."
Yes, but unless you reindex won't your query efficiency be reduced because the average number of pages traversed will increase. In effect, the data structure will be lopsided toward the latest inserted values:
/\
../\
.../\
...../\
....../\
instead of:
../\
./\/\
/\/\/\
blindman|||An int as a foreign key will be faster than a wider key because it takes up less space and less page reads.
It can cause the system to be slower though because to obtain any data from the other table you will need to join to it. Using the natural key the data in that key is available on the referred table.
Not so bad with just two tables but
t1 (col1, col2, col3)
t2 (t1_col1, t1_co2,t1_col3)
t3 (t1_col3)
This is quite common. With the natural keys you can join t2 and t3. If you replace with artificial keys you would have to join to t1 as well so causing more page reads and using up more memory.
It's a subject that people get very passionate about. I disagree with those that say that all tables should have an artificial key and this should always be used for the join field but wouldn't disagree that it can sometimes be useful to create one for efficiency or ease of coding.|||well, that's why after going through structural normalization process designers revise the design with DE-normalization steps. it still is an evidence of consistency and is easy to follow. btw, you don't have to join t1 in the example you give while using "artificial keys".|||The B-tree still maintains ranges but yes it is a trade-off, if you are heavy reads, low inserts, you should not use this method, but if you are heavy inserts with medium to heavy reads, this is good for minimizing page splits without having to play with fill factors.
Nigel, I'm one of those that recommends a surrogate key on every table, just seen too many times a company changes their primary key algorithm and it makes things easier in my opinion when you push to a warehouse. But I won't argue that passionately for it, to each his own :)
Originally posted by blindman
"(2) What type of inserts do you have, the nice thing about having an identity as a PK and clustered index is all inserts fall to the last leaf level thereby reducing page splits."
Yes, but unless you reindex won't your query efficiency be reduced because the average number of pages traversed will increase. In effect, the data structure will be lopsided toward the latest inserted values:
/\
../\
.../\
...../\
....../\
instead of:
../\
./\/\
/\/\/\
blindman|||I used to use natural keys a lot, but I found that the lower-level tables in schemas designed with natural keys tended to have multi-column indexes with multiple joins to related tables. I still consider using natural keys for higher-level tables, especially look-up tables.
Consider the needs of your application as well. By standardizing the key you simplify things for developers who always know what type of field to submit for searches, etc. If you use a natural key and then decide you want to change it (length or type) you may force changes in your middle tier.
...and one other cool thing I was able to do with GUIDs. We had developers who wanted to be able to lookup information based on the ID of an employee record, but they also wanted the same functionality by submitting the session-specific security token the employee was using. Since both were GUIDs, I was able to create a single procedure which required only a single parameter @.EmployeeIDorToken, and determine within the procedure which one was submitted. I could count on the fact that the same GUID would never exist in both tables.
blindman
Regular intermittent Kerberos failures
Hi guys,
This is a last desperate call for help. About once a week, for between
2 and 10 minutes, users are unable to log in to our main web
application (ASP based). They get the following message:
'Failed to generate SSPI context'
Looking at the System Log on the web server displays the following
messages for the web site and SQL SPNs:
'The Security System detected an authentication error for the server
HTTP/<website name>. The failure code from authentication protocol
Kerberos was "The time at the Primary Domain Controller is different
than the time at the Backup Domain Controller or member server by too
large an amount.
(0xc0000133)".'
' The Security System detected an authentication error for the server
MSSQLSvc/S05010010.corp.dnsdom.net:1433. The failure code from
authentication protocol Kerberos was "The time at the Primary Domain
Controller is different than the time at the Backup Domain Controller
or member server by too large an amount.
(0xc0000133)".'
I have used net time to check the times on the Domain Controller, web
server and db server. Can't see any problems. Our system guys have
been through the 'Failed to generate SSPI context' knowledge base
articles.
I haven't seen anything referring to this as a regularly repeating
intermittent problem. We are getting worried cos there is always the
chance it won't come back up!
Any help very gratefully received.
Cheers,
JamesHi James
At a guess this could be a network failure, although if there is a pattern
to the times this occur it would point to something which is scheduled such
as AV or IDS software.
To eliminate the time difference being an issue you may want to try
syncronising both servers with an external time source and not rely on the AD.
John
"JimLad" wrote:
> Hi guys,
> This is a last desperate call for help. About once a week, for between
> 2 and 10 minutes, users are unable to log in to our main web
> application (ASP based). They get the following message:
> 'Failed to generate SSPI context'
> Looking at the System Log on the web server displays the following
> messages for the web site and SQL SPNs:
> 'The Security System detected an authentication error for the server
> HTTP/<website name>. The failure code from authentication protocol
> Kerberos was "The time at the Primary Domain Controller is different
> than the time at the Backup Domain Controller or member server by too
> large an amount.
> (0xc0000133)".'
> ' The Security System detected an authentication error for the server
> MSSQLSvc/S05010010.corp.dnsdom.net:1433. The failure code from
> authentication protocol Kerberos was "The time at the Primary Domain
> Controller is different than the time at the Backup Domain Controller
> or member server by too large an amount.
> (0xc0000133)".'
> I have used net time to check the times on the Domain Controller, web
> server and db server. Can't see any problems. Our system guys have
> been through the 'Failed to generate SSPI context' knowledge base
> articles.
> I haven't seen anything referring to this as a regularly repeating
> intermittent problem. We are getting worried cos there is always the
> chance it won't come back up!
> Any help very gratefully received.
> Cheers,
> James
>|||The messages you posted indicate an Active Directory configuration
problem rather than a SQL Server problem.
>From the information you've provided, its impossible to diagnose what
the problem is without knowing the architecture of your active
directory forest -- whether the HTTP server that logs into your SQL
Server is a member of the domain (which it sounds like it is), and
whether it goes thorough a firewall or any proxy servers that maybe
caching old records.
While Active Directory identifies clients connecting to servers,
Kerberos (which is a layer that runs ontop of active directory for
Microsoft platforms) also authenticates a server to the client. If
the servers are farmed, or there are many secondary domain
controllers, kerberos will check that they are all true mirrors of
each other to prevent somebody from setting up an unauthorized
secondary domain controller to spoof your forest (and thereby allow
unauthorized access via bogus active directory account entries on the
spoofed controller).|||On Aug 24, 5:43 pm, Andy <ane...@.infotek-consulting.com> wrote:
> The messages you posted indicate an Active Directory configuration
> problem rather than a SQL Server problem.
> >From the information you've provided, its impossible to diagnose what
> the problem is without knowing the architecture of your active
> directory forest -- whether the HTTP server that logs into your SQL
> Server is a member of the domain (which it sounds like it is), and
> whether it goes thorough a firewall or any proxy servers that maybe
> caching old records.
> While Active Directory identifies clients connecting to servers,
> Kerberos (which is a layer that runs ontop of active directory for
> Microsoft platforms) also authenticates a server to the client. If
> the servers are farmed, or there are many secondary domain
> controllers, kerberos will check that they are all true mirrors of
> each other to prevent somebody from setting up an unauthorized
> secondary domain controller to spoof your forest (and thereby allow
> unauthorized access via bogus active directory account entries on the
> spoofed controller).
Hi Andy,
Thanks for that. The messages indicate a timing problem: given that
Kerberos only requires servers to be within 5 minutes is this a case
of a misleading error message or is it that I am not using net time on
enough domain controllers? I also notice that the Kerberos group
policy "Maximum Tolerance for
Computer Clock Synchronization" is 'Not Defined'. Does this need to
be
defined or will it automatically use the default of 5 minutes?
Would turning on Kerberos event logging help to diagnose this? Would
turning it on on the web server be sufficient or would it need to be
enabled on dcs and db server as well? And would turning it on be a bad
idea on a production system?
More info: there is a single web server and a single db server, based
in London, no proxy or firewall between them. There are 4 domain
controllers in London. All of these are in the same domain. There are
other domain controllers at other locations in the same domain.
Any ideas on how to diagnose the problem would be extremely welcome.
Many thanks.
Cheers,
James|||On Aug 28, 10:01 am, JimLad <jamesdbi...@.yahoo.co.uk> wrote:
> On Aug 24, 5:43 pm, Andy <ane...@.infotek-consulting.com> wrote:
>
>
> > The messages you posted indicate an Active Directory configuration
> > problem rather than a SQL Server problem.
> > >From the information you've provided, its impossible to diagnose what
> > the problem is without knowing the architecture of your active
> > directory forest -- whether the HTTP server that logs into your SQL
> > Server is a member of the domain (which it sounds like it is), and
> > whether it goes thorough a firewall or any proxy servers that maybe
> > caching old records.
> > While Active Directory identifies clients connecting to servers,
> > Kerberos (which is a layer that runs ontop of active directory for
> > Microsoft platforms) also authenticates a server to the client. If
> > the servers are farmed, or there are many secondary domain
> > controllers, kerberos will check that they are all true mirrors of
> > each other to prevent somebody from setting up an unauthorized
> > secondary domain controller to spoof your forest (and thereby allow
> > unauthorized access via bogus active directory account entries on the
> > spoofed controller).
> Hi Andy,
> Thanks for that. The messages indicate a timing problem: given that
> Kerberos only requires servers to be within 5 minutes is this a case
> of a misleading error message or is it that I am not using net time on
> enough domain controllers? I also notice that the Kerberos group
> policy "Maximum Tolerance for
> Computer Clock Synchronization" is 'Not Defined'. Does this need to
> be
> defined or will it automatically use the default of 5 minutes?
> Would turning on Kerberos event logging help to diagnose this? Would
> turning it on on the web server be sufficient or would it need to be
> enabled on dcs and db server as well? And would turning it on be a bad
> idea on a production system?
> More info: there is a single web server and a single db server, based
> in London, no proxy or firewall between them. There are 4 domain
> controllers in London. All of these are in the same domain. There are
> other domain controllers at other locations in the same domain.
> Any ideas on how to diagnose the problem would be extremely welcome.
> Many thanks.
> Cheers,
> James- Hide quoted text -
> - Show quoted text -
Hi,
We turned on Kerberos tracing and in the 16 seconds that it didn't
work this week we got the following messages on the web server:
Event Type: Error
Event Source: Kerberos
Event Category: None
Event ID: 3
Date: 30/08/2007
Time: 17:01:38
User: N/A
Computer: S05010072
Description:
A Kerberos Error Message was received:
on logon session
Client Time:
Server Time: 16:1:39.0000 8/30/2007 Z
Error Code: 0xb KDC_ERR_NEVER_VALID
Extended Error: 0xc0000133 KLIN(0)
Client Realm:
Client Name:
Server Realm: CORP.DNSDOM.NET
Server Name: MSSQLSvc/S05010010.corp.dnsdom.net:1433
Target Name: MSSQLSvc/S05010010.corp.dnsdom.net:1433@.CORP.DNSDOM.NET
Error Text:
File: 9
Line: ae0
Error Data is in record data.
Event Type: Error
Event Source: Kerberos
Event Category: None
Event ID: 3
Date: 30/08/2007
Time: 17:01:47
User: N/A
Computer: S05010072
Description:
A Kerberos Error Message was received:
on logon session
Client Time:
Server Time: 16:1:49.0000 8/30/2007 Z
Error Code: 0xb KDC_ERR_NEVER_VALID
Extended Error: 0xc0000133 KLIN(0)
Client Realm:
Client Name:
Server Realm: CORP.DNSDOM.NET
Server Name: HTTP/<websitehostheader>
Target Name: HTTP/<websitehostheader>@.CORP.DNSDOM.NET
Error Text:
File: 9
Line: ae0
Error Data is in record data.
0xB - KDC_ERR_NEVER_VALID: Requested start time is later than end time
Associated internal Windows error codes
=B7None
Corresponding debug output messages
=B7DebugLog("Client asked for endtime before starttime\n")
Possible Cause and Resolution
=B7There is a time difference between the KDC and the client.
Resolution
For Kerberos authentication to work, you must synchronize clocks on
the client and on the server. For more information about this error
and how to resolve it, see Time Synchronization (Clock Skew) earlier
in this white paper.
Any ideas why we would get this error message once a week for a window
of between a few seconds and 10 minutes?
Is there any way of knowing where the KDC is? I assume it's one of the
domain controllers, but as we have several is there a way of knowing
which is being used?
We have also been getting non-fatal Kerberos messages (0x25
KRB_AP_ERR_SKEW) about the time on file server S20. This isn't a DC
and isn't involved in the authentication so I'm not sure why we are
getting this message, even though that server is indeed 6 minutes
fast.
Outside this time window we get lots of the following messages:
0x34 KRB_ERR_RESPONSE_TOO_BIG
0xd KDC_ERR_BADOPTION
0x7 KDC_ERR_S_PRINCIPAL_UNKNOWN
0x25 KRB_AP_ERR_SKEW
Cheers,
James|||Hi,
To answer some questions:
KDC runs on all Domain Controllers by default. You need to use a tool like
KerbTray or KList to see where the Kerberos tickets in question are coming
from
> We have also been getting non-fatal Kerberos messages (0x25
> KRB_AP_ERR_SKEW) about the time on file server S20. This isn't a DC
> and isn't involved in the authentication so I'm not sure why we are
> getting this message, even though that server is indeed 6 minutes
> fast.
Well, machines also authenticate to each other.
> 0x34 KRB_ERR_RESPONSE_TOO_BIG
Generally means that the packet was too big to be transmitted and was
fragmented. Should generally be OK, because Kerberos can be sent over TCP
rather than just UDP.
> 0x7 KDC_ERR_S_PRINCIPAL_UNKNOWN
An SPN is missing from within your Active Directory
> 0x25 KRB_AP_ERR_SKEW
Time is out by more than the permitted deviation.
It looks like you have some time sync issues in your organisation. Are you
using the default Windows time sync heirachy (by default all DCs sync time
with the PDCe FSMO role holder, and all clients sync with their
authenticating DCs), or have you overriden this in some way?
Cheers
Ken
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:1188491704.797545.33500@.50g2000hsm.googlegroups.com...
On Aug 28, 10:01 am, JimLad <jamesdbi...@.yahoo.co.uk> wrote:
> On Aug 24, 5:43 pm, Andy <ane...@.infotek-consulting.com> wrote:
>
>
> > The messages you posted indicate an Active Directory configuration
> > problem rather than a SQL Server problem.
> > >From the information you've provided, its impossible to diagnose what
> > the problem is without knowing the architecture of your active
> > directory forest -- whether the HTTP server that logs into your SQL
> > Server is a member of the domain (which it sounds like it is), and
> > whether it goes thorough a firewall or any proxy servers that maybe
> > caching old records.
> > While Active Directory identifies clients connecting to servers,
> > Kerberos (which is a layer that runs ontop of active directory for
> > Microsoft platforms) also authenticates a server to the client. If
> > the servers are farmed, or there are many secondary domain
> > controllers, kerberos will check that they are all true mirrors of
> > each other to prevent somebody from setting up an unauthorized
> > secondary domain controller to spoof your forest (and thereby allow
> > unauthorized access via bogus active directory account entries on the
> > spoofed controller).
> Hi Andy,
> Thanks for that. The messages indicate a timing problem: given that
> Kerberos only requires servers to be within 5 minutes is this a case
> of a misleading error message or is it that I am not using net time on
> enough domain controllers? I also notice that the Kerberos group
> policy "Maximum Tolerance for
> Computer Clock Synchronization" is 'Not Defined'. Does this need to
> be
> defined or will it automatically use the default of 5 minutes?
> Would turning on Kerberos event logging help to diagnose this? Would
> turning it on on the web server be sufficient or would it need to be
> enabled on dcs and db server as well? And would turning it on be a bad
> idea on a production system?
> More info: there is a single web server and a single db server, based
> in London, no proxy or firewall between them. There are 4 domain
> controllers in London. All of these are in the same domain. There are
> other domain controllers at other locations in the same domain.
> Any ideas on how to diagnose the problem would be extremely welcome.
> Many thanks.
> Cheers,
> James- Hide quoted text -
> - Show quoted text -
Hi,
We turned on Kerberos tracing and in the 16 seconds that it didn't
work this week we got the following messages on the web server:
Event Type: Error
Event Source: Kerberos
Event Category: None
Event ID: 3
Date: 30/08/2007
Time: 17:01:38
User: N/A
Computer: S05010072
Description:
A Kerberos Error Message was received:
on logon session
Client Time:
Server Time: 16:1:39.0000 8/30/2007 Z
Error Code: 0xb KDC_ERR_NEVER_VALID
Extended Error: 0xc0000133 KLIN(0)
Client Realm:
Client Name:
Server Realm: CORP.DNSDOM.NET
Server Name: MSSQLSvc/S05010010.corp.dnsdom.net:1433
Target Name: MSSQLSvc/S05010010.corp.dnsdom.net:1433@.CORP.DNSDOM.NET
Error Text:
File: 9
Line: ae0
Error Data is in record data.
Event Type: Error
Event Source: Kerberos
Event Category: None
Event ID: 3
Date: 30/08/2007
Time: 17:01:47
User: N/A
Computer: S05010072
Description:
A Kerberos Error Message was received:
on logon session
Client Time:
Server Time: 16:1:49.0000 8/30/2007 Z
Error Code: 0xb KDC_ERR_NEVER_VALID
Extended Error: 0xc0000133 KLIN(0)
Client Realm:
Client Name:
Server Realm: CORP.DNSDOM.NET
Server Name: HTTP/<websitehostheader>
Target Name: HTTP/<websitehostheader>@.CORP.DNSDOM.NET
Error Text:
File: 9
Line: ae0
Error Data is in record data.
0xB - KDC_ERR_NEVER_VALID: Requested start time is later than end time
Associated internal Windows error codes
·None
Corresponding debug output messages
·DebugLog("Client asked for endtime before starttime\n")
Possible Cause and Resolution
·There is a time difference between the KDC and the client.
Resolution
For Kerberos authentication to work, you must synchronize clocks on
the client and on the server. For more information about this error
and how to resolve it, see Time Synchronization (Clock Skew) earlier
in this white paper.
Any ideas why we would get this error message once a week for a window
of between a few seconds and 10 minutes?
Is there any way of knowing where the KDC is? I assume it's one of the
domain controllers, but as we have several is there a way of knowing
which is being used?
We have also been getting non-fatal Kerberos messages (0x25
KRB_AP_ERR_SKEW) about the time on file server S20. This isn't a DC
and isn't involved in the authentication so I'm not sure why we are
getting this message, even though that server is indeed 6 minutes
fast.
Outside this time window we get lots of the following messages:
0x34 KRB_ERR_RESPONSE_TOO_BIG
0xd KDC_ERR_BADOPTION
0x7 KDC_ERR_S_PRINCIPAL_UNKNOWN
0x25 KRB_AP_ERR_SKEW
Cheers,
James|||On Sep 4, 6:50 am, "Ken Schaefer" <kenREM...@.THISadOpenStatic.com>
wrote:
> Hi,
> To answer some questions:
> KDC runs on all Domain Controllers by default. You need to use a tool like
> KerbTray or KList to see where the Kerberos tickets in question are coming
> from
> > We have also been getting non-fatal Kerberos messages (0x25
> > KRB_AP_ERR_SKEW) about the time on file server S20. This isn't a DC
> > and isn't involved in the authentication so I'm not sure why we are
> > getting this message, even though that server is indeed 6 minutes
> > fast.
> Well, machines also authenticate to each other.
> > 0x34 KRB_ERR_RESPONSE_TOO_BIG
> Generally means that the packet was too big to be transmitted and was
> fragmented. Should generally be OK, because Kerberos can be sent over TCP
> rather than just UDP.
> > 0x7 KDC_ERR_S_PRINCIPAL_UNKNOWN
> An SPN is missing from within your Active Directory
> > 0x25 KRB_AP_ERR_SKEW
> Time is out by more than the permitted deviation.
> It looks like you have some time sync issues in your organisation. Are you
> using the default Windows time sync heirachy (by default all DCs sync time
> with the PDCe FSMO role holder, and all clients sync with their
> authenticating DCs), or have you overriden this in some way?
> Cheers
> Ken
> "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> news:1188491704.797545.33500@.50g2000hsm.googlegroups.com...
> On Aug 28, 10:01 am, JimLad <jamesdbi...@.yahoo.co.uk> wrote:
>
>
> > On Aug 24, 5:43 pm, Andy <ane...@.infotek-consulting.com> wrote:
> > > The messages you posted indicate an Active Directory configuration
> > > problem rather than a SQL Server problem.
> > > >From the information you've provided, its impossible to diagnose what
> > > the problem is without knowing the architecture of your active
> > > directory forest -- whether the HTTP server that logs into your SQL
> > > Server is a member of the domain (which it sounds like it is), and
> > > whether it goes thorough a firewall or any proxy servers that maybe
> > > caching old records.
> > > While Active Directory identifies clients connecting to servers,
> > > Kerberos (which is a layer that runs ontop of active directory for
> > > Microsoft platforms) also authenticates a server to the client. If
> > > the servers are farmed, or there are many secondary domain
> > > controllers, kerberos will check that they are all true mirrors of
> > > each other to prevent somebody from setting up an unauthorized
> > > secondary domain controller to spoof your forest (and thereby allow
> > > unauthorized access via bogus active directory account entries on the
> > > spoofed controller).
> > Hi Andy,
> > Thanks for that. The messages indicate a timing problem: given that
> > Kerberos only requires servers to be within 5 minutes is this a case
> > of a misleading error message or is it that I am not using net time on
> > enough domain controllers? I also notice that the Kerberos group
> > policy "Maximum Tolerance for
> > Computer Clock Synchronization" is 'Not Defined'. Does this need to
> > be
> > defined or will it automatically use the default of 5 minutes?
> > Would turning on Kerberos event logging help to diagnose this? Would
> > turning it on on the web server be sufficient or would it need to be
> > enabled on dcs and db server as well? And would turning it on be a bad
> > idea on a production system?
> > More info: there is a single web server and a single db server, based
> > in London, no proxy or firewall between them. There are 4 domain
> > controllers in London. All of these are in the same domain. There are
> > other domain controllers at other locations in the same domain.
> > Any ideas on how to diagnose the problem would be extremely welcome.
> > Many thanks.
> > Cheers,
> > James- Hide quoted text -
> > - Show quoted text -
> Hi,
> We turned on Kerberos tracing and in the 16 seconds that it didn't
> work this week we got the following messages on the web server:
> Event Type: Error
> Event Source: Kerberos
> Event Category: None
> Event ID: 3
> Date: 30/08/2007
> Time: 17:01:38
> User: N/A
> Computer: S05010072
> Description:
> A Kerberos Error Message was received:
> on logon session
> Client Time:
> Server Time: 16:1:39.0000 8/30/2007 Z
> Error Code: 0xb KDC_ERR_NEVER_VALID
> Extended Error: 0xc0000133 KLIN(0)
> Client Realm:
> Client Name:
> Server Realm: CORP.DNSDOM.NET
> Server Name: MSSQLSvc/S05010010.corp.dnsdom.net:1433
> Target Name: MSSQLSvc/S05010010.corp.dnsdom.net:1...@.CORP.DNSDOM.NET
> Error Text:
> File: 9
> Line: ae0
> Error Data is in record data.
> Event Type: Error
> Event Source: Kerberos
> Event Category: None
> Event ID: 3
> Date: 30/08/2007
> Time: 17:01:47
> User: N/A
> Computer: S05010072
> Description:
> A Kerberos Error Message was received:
> on logon session
> Client Time:
> Server Time: 16:1:49.0000 8/30/2007 Z
> Error Code: 0xb KDC_ERR_NEVER_VALID
> Extended Error: 0xc0000133 KLIN(0)
> Client Realm:
> Client Name:
> Server Realm: CORP.DNSDOM.NET
> Server Name: HTTP/<websitehostheader>
> Target Name: HTTP/<websitehostheader>@.CORP.DNSDOM.NET
> Error Text:
> File: 9
> Line: ae0
> Error Data is in record data.
> 0xB - KDC_ERR_NEVER_VALID: Requested start time is later than end time
> Associated internal Windows error codes
> =B7None
> Corresponding debug output messages
> =B7DebugLog("Client asked for endtime before starttime\n")
> Possible Cause and Resolution
> =B7There is a time difference between the KDC and the client.
> Resolution
> For Kerberos authentication to work, you must synchronize clocks on
> the client and on the server. For more information about this error
> and how to resolve it, see Time Synchronization (Clock Skew) earlier
> in this white paper.
> Any ideas why we would get this error message once a week for a window
> of between a few seconds and 10 minutes?
> Is there any way of knowing where the KDC is? I assume it's one of the
> domain controllers, but as we have several is there a way of knowing
> which is being used?
> We have also been getting non-fatal Kerberos messages (0x25
> KRB_AP_ERR_SKEW) about the time on file server S20. This isn't a DC
> and isn't involved in the authentication so I'm not sure why we are
> getting this message, even though that server is indeed 6 minutes
> fast.
> Outside this time window we get lots of the following messages:
> 0x34 KRB_ERR_RESPONSE_TOO_BIG
> 0xd KDC_ERR_BADOPTION
> 0x7 KDC_ERR_S_PRINCIPAL_UNKNOWN
> 0x25 KRB_AP_ERR_SKEW
> Cheers,
> James- Hide quoted text -
> - Show quoted text -
Thanks Ken. Useful stuff.
I've posted a new subject based on a message I found in the security
log on the DC.
http://groups.google.com/group/comp.protocols.kerberos/browse_thread/thread=
/ce62e8b04e3cddad/5af3d0b03cee0927#5af3d0b03cee0927
Cheers,
James
This is a last desperate call for help. About once a week, for between
2 and 10 minutes, users are unable to log in to our main web
application (ASP based). They get the following message:
'Failed to generate SSPI context'
Looking at the System Log on the web server displays the following
messages for the web site and SQL SPNs:
'The Security System detected an authentication error for the server
HTTP/<website name>. The failure code from authentication protocol
Kerberos was "The time at the Primary Domain Controller is different
than the time at the Backup Domain Controller or member server by too
large an amount.
(0xc0000133)".'
' The Security System detected an authentication error for the server
MSSQLSvc/S05010010.corp.dnsdom.net:1433. The failure code from
authentication protocol Kerberos was "The time at the Primary Domain
Controller is different than the time at the Backup Domain Controller
or member server by too large an amount.
(0xc0000133)".'
I have used net time to check the times on the Domain Controller, web
server and db server. Can't see any problems. Our system guys have
been through the 'Failed to generate SSPI context' knowledge base
articles.
I haven't seen anything referring to this as a regularly repeating
intermittent problem. We are getting worried cos there is always the
chance it won't come back up!
Any help very gratefully received.
Cheers,
JamesHi James
At a guess this could be a network failure, although if there is a pattern
to the times this occur it would point to something which is scheduled such
as AV or IDS software.
To eliminate the time difference being an issue you may want to try
syncronising both servers with an external time source and not rely on the AD.
John
"JimLad" wrote:
> Hi guys,
> This is a last desperate call for help. About once a week, for between
> 2 and 10 minutes, users are unable to log in to our main web
> application (ASP based). They get the following message:
> 'Failed to generate SSPI context'
> Looking at the System Log on the web server displays the following
> messages for the web site and SQL SPNs:
> 'The Security System detected an authentication error for the server
> HTTP/<website name>. The failure code from authentication protocol
> Kerberos was "The time at the Primary Domain Controller is different
> than the time at the Backup Domain Controller or member server by too
> large an amount.
> (0xc0000133)".'
> ' The Security System detected an authentication error for the server
> MSSQLSvc/S05010010.corp.dnsdom.net:1433. The failure code from
> authentication protocol Kerberos was "The time at the Primary Domain
> Controller is different than the time at the Backup Domain Controller
> or member server by too large an amount.
> (0xc0000133)".'
> I have used net time to check the times on the Domain Controller, web
> server and db server. Can't see any problems. Our system guys have
> been through the 'Failed to generate SSPI context' knowledge base
> articles.
> I haven't seen anything referring to this as a regularly repeating
> intermittent problem. We are getting worried cos there is always the
> chance it won't come back up!
> Any help very gratefully received.
> Cheers,
> James
>|||The messages you posted indicate an Active Directory configuration
problem rather than a SQL Server problem.
>From the information you've provided, its impossible to diagnose what
the problem is without knowing the architecture of your active
directory forest -- whether the HTTP server that logs into your SQL
Server is a member of the domain (which it sounds like it is), and
whether it goes thorough a firewall or any proxy servers that maybe
caching old records.
While Active Directory identifies clients connecting to servers,
Kerberos (which is a layer that runs ontop of active directory for
Microsoft platforms) also authenticates a server to the client. If
the servers are farmed, or there are many secondary domain
controllers, kerberos will check that they are all true mirrors of
each other to prevent somebody from setting up an unauthorized
secondary domain controller to spoof your forest (and thereby allow
unauthorized access via bogus active directory account entries on the
spoofed controller).|||On Aug 24, 5:43 pm, Andy <ane...@.infotek-consulting.com> wrote:
> The messages you posted indicate an Active Directory configuration
> problem rather than a SQL Server problem.
> >From the information you've provided, its impossible to diagnose what
> the problem is without knowing the architecture of your active
> directory forest -- whether the HTTP server that logs into your SQL
> Server is a member of the domain (which it sounds like it is), and
> whether it goes thorough a firewall or any proxy servers that maybe
> caching old records.
> While Active Directory identifies clients connecting to servers,
> Kerberos (which is a layer that runs ontop of active directory for
> Microsoft platforms) also authenticates a server to the client. If
> the servers are farmed, or there are many secondary domain
> controllers, kerberos will check that they are all true mirrors of
> each other to prevent somebody from setting up an unauthorized
> secondary domain controller to spoof your forest (and thereby allow
> unauthorized access via bogus active directory account entries on the
> spoofed controller).
Hi Andy,
Thanks for that. The messages indicate a timing problem: given that
Kerberos only requires servers to be within 5 minutes is this a case
of a misleading error message or is it that I am not using net time on
enough domain controllers? I also notice that the Kerberos group
policy "Maximum Tolerance for
Computer Clock Synchronization" is 'Not Defined'. Does this need to
be
defined or will it automatically use the default of 5 minutes?
Would turning on Kerberos event logging help to diagnose this? Would
turning it on on the web server be sufficient or would it need to be
enabled on dcs and db server as well? And would turning it on be a bad
idea on a production system?
More info: there is a single web server and a single db server, based
in London, no proxy or firewall between them. There are 4 domain
controllers in London. All of these are in the same domain. There are
other domain controllers at other locations in the same domain.
Any ideas on how to diagnose the problem would be extremely welcome.
Many thanks.
Cheers,
James|||On Aug 28, 10:01 am, JimLad <jamesdbi...@.yahoo.co.uk> wrote:
> On Aug 24, 5:43 pm, Andy <ane...@.infotek-consulting.com> wrote:
>
>
> > The messages you posted indicate an Active Directory configuration
> > problem rather than a SQL Server problem.
> > >From the information you've provided, its impossible to diagnose what
> > the problem is without knowing the architecture of your active
> > directory forest -- whether the HTTP server that logs into your SQL
> > Server is a member of the domain (which it sounds like it is), and
> > whether it goes thorough a firewall or any proxy servers that maybe
> > caching old records.
> > While Active Directory identifies clients connecting to servers,
> > Kerberos (which is a layer that runs ontop of active directory for
> > Microsoft platforms) also authenticates a server to the client. If
> > the servers are farmed, or there are many secondary domain
> > controllers, kerberos will check that they are all true mirrors of
> > each other to prevent somebody from setting up an unauthorized
> > secondary domain controller to spoof your forest (and thereby allow
> > unauthorized access via bogus active directory account entries on the
> > spoofed controller).
> Hi Andy,
> Thanks for that. The messages indicate a timing problem: given that
> Kerberos only requires servers to be within 5 minutes is this a case
> of a misleading error message or is it that I am not using net time on
> enough domain controllers? I also notice that the Kerberos group
> policy "Maximum Tolerance for
> Computer Clock Synchronization" is 'Not Defined'. Does this need to
> be
> defined or will it automatically use the default of 5 minutes?
> Would turning on Kerberos event logging help to diagnose this? Would
> turning it on on the web server be sufficient or would it need to be
> enabled on dcs and db server as well? And would turning it on be a bad
> idea on a production system?
> More info: there is a single web server and a single db server, based
> in London, no proxy or firewall between them. There are 4 domain
> controllers in London. All of these are in the same domain. There are
> other domain controllers at other locations in the same domain.
> Any ideas on how to diagnose the problem would be extremely welcome.
> Many thanks.
> Cheers,
> James- Hide quoted text -
> - Show quoted text -
Hi,
We turned on Kerberos tracing and in the 16 seconds that it didn't
work this week we got the following messages on the web server:
Event Type: Error
Event Source: Kerberos
Event Category: None
Event ID: 3
Date: 30/08/2007
Time: 17:01:38
User: N/A
Computer: S05010072
Description:
A Kerberos Error Message was received:
on logon session
Client Time:
Server Time: 16:1:39.0000 8/30/2007 Z
Error Code: 0xb KDC_ERR_NEVER_VALID
Extended Error: 0xc0000133 KLIN(0)
Client Realm:
Client Name:
Server Realm: CORP.DNSDOM.NET
Server Name: MSSQLSvc/S05010010.corp.dnsdom.net:1433
Target Name: MSSQLSvc/S05010010.corp.dnsdom.net:1433@.CORP.DNSDOM.NET
Error Text:
File: 9
Line: ae0
Error Data is in record data.
Event Type: Error
Event Source: Kerberos
Event Category: None
Event ID: 3
Date: 30/08/2007
Time: 17:01:47
User: N/A
Computer: S05010072
Description:
A Kerberos Error Message was received:
on logon session
Client Time:
Server Time: 16:1:49.0000 8/30/2007 Z
Error Code: 0xb KDC_ERR_NEVER_VALID
Extended Error: 0xc0000133 KLIN(0)
Client Realm:
Client Name:
Server Realm: CORP.DNSDOM.NET
Server Name: HTTP/<websitehostheader>
Target Name: HTTP/<websitehostheader>@.CORP.DNSDOM.NET
Error Text:
File: 9
Line: ae0
Error Data is in record data.
0xB - KDC_ERR_NEVER_VALID: Requested start time is later than end time
Associated internal Windows error codes
=B7None
Corresponding debug output messages
=B7DebugLog("Client asked for endtime before starttime\n")
Possible Cause and Resolution
=B7There is a time difference between the KDC and the client.
Resolution
For Kerberos authentication to work, you must synchronize clocks on
the client and on the server. For more information about this error
and how to resolve it, see Time Synchronization (Clock Skew) earlier
in this white paper.
Any ideas why we would get this error message once a week for a window
of between a few seconds and 10 minutes?
Is there any way of knowing where the KDC is? I assume it's one of the
domain controllers, but as we have several is there a way of knowing
which is being used?
We have also been getting non-fatal Kerberos messages (0x25
KRB_AP_ERR_SKEW) about the time on file server S20. This isn't a DC
and isn't involved in the authentication so I'm not sure why we are
getting this message, even though that server is indeed 6 minutes
fast.
Outside this time window we get lots of the following messages:
0x34 KRB_ERR_RESPONSE_TOO_BIG
0xd KDC_ERR_BADOPTION
0x7 KDC_ERR_S_PRINCIPAL_UNKNOWN
0x25 KRB_AP_ERR_SKEW
Cheers,
James|||Hi,
To answer some questions:
KDC runs on all Domain Controllers by default. You need to use a tool like
KerbTray or KList to see where the Kerberos tickets in question are coming
from
> We have also been getting non-fatal Kerberos messages (0x25
> KRB_AP_ERR_SKEW) about the time on file server S20. This isn't a DC
> and isn't involved in the authentication so I'm not sure why we are
> getting this message, even though that server is indeed 6 minutes
> fast.
Well, machines also authenticate to each other.
> 0x34 KRB_ERR_RESPONSE_TOO_BIG
Generally means that the packet was too big to be transmitted and was
fragmented. Should generally be OK, because Kerberos can be sent over TCP
rather than just UDP.
> 0x7 KDC_ERR_S_PRINCIPAL_UNKNOWN
An SPN is missing from within your Active Directory
> 0x25 KRB_AP_ERR_SKEW
Time is out by more than the permitted deviation.
It looks like you have some time sync issues in your organisation. Are you
using the default Windows time sync heirachy (by default all DCs sync time
with the PDCe FSMO role holder, and all clients sync with their
authenticating DCs), or have you overriden this in some way?
Cheers
Ken
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:1188491704.797545.33500@.50g2000hsm.googlegroups.com...
On Aug 28, 10:01 am, JimLad <jamesdbi...@.yahoo.co.uk> wrote:
> On Aug 24, 5:43 pm, Andy <ane...@.infotek-consulting.com> wrote:
>
>
> > The messages you posted indicate an Active Directory configuration
> > problem rather than a SQL Server problem.
> > >From the information you've provided, its impossible to diagnose what
> > the problem is without knowing the architecture of your active
> > directory forest -- whether the HTTP server that logs into your SQL
> > Server is a member of the domain (which it sounds like it is), and
> > whether it goes thorough a firewall or any proxy servers that maybe
> > caching old records.
> > While Active Directory identifies clients connecting to servers,
> > Kerberos (which is a layer that runs ontop of active directory for
> > Microsoft platforms) also authenticates a server to the client. If
> > the servers are farmed, or there are many secondary domain
> > controllers, kerberos will check that they are all true mirrors of
> > each other to prevent somebody from setting up an unauthorized
> > secondary domain controller to spoof your forest (and thereby allow
> > unauthorized access via bogus active directory account entries on the
> > spoofed controller).
> Hi Andy,
> Thanks for that. The messages indicate a timing problem: given that
> Kerberos only requires servers to be within 5 minutes is this a case
> of a misleading error message or is it that I am not using net time on
> enough domain controllers? I also notice that the Kerberos group
> policy "Maximum Tolerance for
> Computer Clock Synchronization" is 'Not Defined'. Does this need to
> be
> defined or will it automatically use the default of 5 minutes?
> Would turning on Kerberos event logging help to diagnose this? Would
> turning it on on the web server be sufficient or would it need to be
> enabled on dcs and db server as well? And would turning it on be a bad
> idea on a production system?
> More info: there is a single web server and a single db server, based
> in London, no proxy or firewall between them. There are 4 domain
> controllers in London. All of these are in the same domain. There are
> other domain controllers at other locations in the same domain.
> Any ideas on how to diagnose the problem would be extremely welcome.
> Many thanks.
> Cheers,
> James- Hide quoted text -
> - Show quoted text -
Hi,
We turned on Kerberos tracing and in the 16 seconds that it didn't
work this week we got the following messages on the web server:
Event Type: Error
Event Source: Kerberos
Event Category: None
Event ID: 3
Date: 30/08/2007
Time: 17:01:38
User: N/A
Computer: S05010072
Description:
A Kerberos Error Message was received:
on logon session
Client Time:
Server Time: 16:1:39.0000 8/30/2007 Z
Error Code: 0xb KDC_ERR_NEVER_VALID
Extended Error: 0xc0000133 KLIN(0)
Client Realm:
Client Name:
Server Realm: CORP.DNSDOM.NET
Server Name: MSSQLSvc/S05010010.corp.dnsdom.net:1433
Target Name: MSSQLSvc/S05010010.corp.dnsdom.net:1433@.CORP.DNSDOM.NET
Error Text:
File: 9
Line: ae0
Error Data is in record data.
Event Type: Error
Event Source: Kerberos
Event Category: None
Event ID: 3
Date: 30/08/2007
Time: 17:01:47
User: N/A
Computer: S05010072
Description:
A Kerberos Error Message was received:
on logon session
Client Time:
Server Time: 16:1:49.0000 8/30/2007 Z
Error Code: 0xb KDC_ERR_NEVER_VALID
Extended Error: 0xc0000133 KLIN(0)
Client Realm:
Client Name:
Server Realm: CORP.DNSDOM.NET
Server Name: HTTP/<websitehostheader>
Target Name: HTTP/<websitehostheader>@.CORP.DNSDOM.NET
Error Text:
File: 9
Line: ae0
Error Data is in record data.
0xB - KDC_ERR_NEVER_VALID: Requested start time is later than end time
Associated internal Windows error codes
·None
Corresponding debug output messages
·DebugLog("Client asked for endtime before starttime\n")
Possible Cause and Resolution
·There is a time difference between the KDC and the client.
Resolution
For Kerberos authentication to work, you must synchronize clocks on
the client and on the server. For more information about this error
and how to resolve it, see Time Synchronization (Clock Skew) earlier
in this white paper.
Any ideas why we would get this error message once a week for a window
of between a few seconds and 10 minutes?
Is there any way of knowing where the KDC is? I assume it's one of the
domain controllers, but as we have several is there a way of knowing
which is being used?
We have also been getting non-fatal Kerberos messages (0x25
KRB_AP_ERR_SKEW) about the time on file server S20. This isn't a DC
and isn't involved in the authentication so I'm not sure why we are
getting this message, even though that server is indeed 6 minutes
fast.
Outside this time window we get lots of the following messages:
0x34 KRB_ERR_RESPONSE_TOO_BIG
0xd KDC_ERR_BADOPTION
0x7 KDC_ERR_S_PRINCIPAL_UNKNOWN
0x25 KRB_AP_ERR_SKEW
Cheers,
James|||On Sep 4, 6:50 am, "Ken Schaefer" <kenREM...@.THISadOpenStatic.com>
wrote:
> Hi,
> To answer some questions:
> KDC runs on all Domain Controllers by default. You need to use a tool like
> KerbTray or KList to see where the Kerberos tickets in question are coming
> from
> > We have also been getting non-fatal Kerberos messages (0x25
> > KRB_AP_ERR_SKEW) about the time on file server S20. This isn't a DC
> > and isn't involved in the authentication so I'm not sure why we are
> > getting this message, even though that server is indeed 6 minutes
> > fast.
> Well, machines also authenticate to each other.
> > 0x34 KRB_ERR_RESPONSE_TOO_BIG
> Generally means that the packet was too big to be transmitted and was
> fragmented. Should generally be OK, because Kerberos can be sent over TCP
> rather than just UDP.
> > 0x7 KDC_ERR_S_PRINCIPAL_UNKNOWN
> An SPN is missing from within your Active Directory
> > 0x25 KRB_AP_ERR_SKEW
> Time is out by more than the permitted deviation.
> It looks like you have some time sync issues in your organisation. Are you
> using the default Windows time sync heirachy (by default all DCs sync time
> with the PDCe FSMO role holder, and all clients sync with their
> authenticating DCs), or have you overriden this in some way?
> Cheers
> Ken
> "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> news:1188491704.797545.33500@.50g2000hsm.googlegroups.com...
> On Aug 28, 10:01 am, JimLad <jamesdbi...@.yahoo.co.uk> wrote:
>
>
> > On Aug 24, 5:43 pm, Andy <ane...@.infotek-consulting.com> wrote:
> > > The messages you posted indicate an Active Directory configuration
> > > problem rather than a SQL Server problem.
> > > >From the information you've provided, its impossible to diagnose what
> > > the problem is without knowing the architecture of your active
> > > directory forest -- whether the HTTP server that logs into your SQL
> > > Server is a member of the domain (which it sounds like it is), and
> > > whether it goes thorough a firewall or any proxy servers that maybe
> > > caching old records.
> > > While Active Directory identifies clients connecting to servers,
> > > Kerberos (which is a layer that runs ontop of active directory for
> > > Microsoft platforms) also authenticates a server to the client. If
> > > the servers are farmed, or there are many secondary domain
> > > controllers, kerberos will check that they are all true mirrors of
> > > each other to prevent somebody from setting up an unauthorized
> > > secondary domain controller to spoof your forest (and thereby allow
> > > unauthorized access via bogus active directory account entries on the
> > > spoofed controller).
> > Hi Andy,
> > Thanks for that. The messages indicate a timing problem: given that
> > Kerberos only requires servers to be within 5 minutes is this a case
> > of a misleading error message or is it that I am not using net time on
> > enough domain controllers? I also notice that the Kerberos group
> > policy "Maximum Tolerance for
> > Computer Clock Synchronization" is 'Not Defined'. Does this need to
> > be
> > defined or will it automatically use the default of 5 minutes?
> > Would turning on Kerberos event logging help to diagnose this? Would
> > turning it on on the web server be sufficient or would it need to be
> > enabled on dcs and db server as well? And would turning it on be a bad
> > idea on a production system?
> > More info: there is a single web server and a single db server, based
> > in London, no proxy or firewall between them. There are 4 domain
> > controllers in London. All of these are in the same domain. There are
> > other domain controllers at other locations in the same domain.
> > Any ideas on how to diagnose the problem would be extremely welcome.
> > Many thanks.
> > Cheers,
> > James- Hide quoted text -
> > - Show quoted text -
> Hi,
> We turned on Kerberos tracing and in the 16 seconds that it didn't
> work this week we got the following messages on the web server:
> Event Type: Error
> Event Source: Kerberos
> Event Category: None
> Event ID: 3
> Date: 30/08/2007
> Time: 17:01:38
> User: N/A
> Computer: S05010072
> Description:
> A Kerberos Error Message was received:
> on logon session
> Client Time:
> Server Time: 16:1:39.0000 8/30/2007 Z
> Error Code: 0xb KDC_ERR_NEVER_VALID
> Extended Error: 0xc0000133 KLIN(0)
> Client Realm:
> Client Name:
> Server Realm: CORP.DNSDOM.NET
> Server Name: MSSQLSvc/S05010010.corp.dnsdom.net:1433
> Target Name: MSSQLSvc/S05010010.corp.dnsdom.net:1...@.CORP.DNSDOM.NET
> Error Text:
> File: 9
> Line: ae0
> Error Data is in record data.
> Event Type: Error
> Event Source: Kerberos
> Event Category: None
> Event ID: 3
> Date: 30/08/2007
> Time: 17:01:47
> User: N/A
> Computer: S05010072
> Description:
> A Kerberos Error Message was received:
> on logon session
> Client Time:
> Server Time: 16:1:49.0000 8/30/2007 Z
> Error Code: 0xb KDC_ERR_NEVER_VALID
> Extended Error: 0xc0000133 KLIN(0)
> Client Realm:
> Client Name:
> Server Realm: CORP.DNSDOM.NET
> Server Name: HTTP/<websitehostheader>
> Target Name: HTTP/<websitehostheader>@.CORP.DNSDOM.NET
> Error Text:
> File: 9
> Line: ae0
> Error Data is in record data.
> 0xB - KDC_ERR_NEVER_VALID: Requested start time is later than end time
> Associated internal Windows error codes
> =B7None
> Corresponding debug output messages
> =B7DebugLog("Client asked for endtime before starttime\n")
> Possible Cause and Resolution
> =B7There is a time difference between the KDC and the client.
> Resolution
> For Kerberos authentication to work, you must synchronize clocks on
> the client and on the server. For more information about this error
> and how to resolve it, see Time Synchronization (Clock Skew) earlier
> in this white paper.
> Any ideas why we would get this error message once a week for a window
> of between a few seconds and 10 minutes?
> Is there any way of knowing where the KDC is? I assume it's one of the
> domain controllers, but as we have several is there a way of knowing
> which is being used?
> We have also been getting non-fatal Kerberos messages (0x25
> KRB_AP_ERR_SKEW) about the time on file server S20. This isn't a DC
> and isn't involved in the authentication so I'm not sure why we are
> getting this message, even though that server is indeed 6 minutes
> fast.
> Outside this time window we get lots of the following messages:
> 0x34 KRB_ERR_RESPONSE_TOO_BIG
> 0xd KDC_ERR_BADOPTION
> 0x7 KDC_ERR_S_PRINCIPAL_UNKNOWN
> 0x25 KRB_AP_ERR_SKEW
> Cheers,
> James- Hide quoted text -
> - Show quoted text -
Thanks Ken. Useful stuff.
I've posted a new subject based on a message I found in the security
log on the DC.
http://groups.google.com/group/comp.protocols.kerberos/browse_thread/thread=
/ce62e8b04e3cddad/5af3d0b03cee0927#5af3d0b03cee0927
Cheers,
James
regular expressions in transact sql
hi guys,
i need some help tp write code in order to search the string ( regular expressions) in t- sql.
e.g.
when a user enters [A-Z] it means any alphabet from 'A' to 'Z'...
similarly [0-9] means any digit.
the problem is: when user enters [0-6] and the string received contains digit 5 it should return true but if it contains 7 it should return false.
so how do i read the [A-Z] as a range of characters in t-sql?
hi,
In sql server it is not exactly regular expression, it is called wild card pattern. in other words it is simplified reqular expression,
as of now SQL Server Like operator only work with following operators
% - Zero or any number of chars
_ - Single Char
[] - Single Char in given range
if you want to utilize the exact regular expression on your query then the best solution will be CLR Functions.(SQL Server 2005).
For fixed validation (only numbers & only alphabets) i achived the following function,
Create Function dbo.IsMatching(@.Value as varchar(1000), @.Pattern as varchar(100))
returns bit as
Begin
Declare @.Len as int;
Declare @.SearchPattern as varchar(8000);
Declare @.Result as Int;
Select @.Len = Len(@.Value);
While @.Len>0
Begin
Select @.SearchPattern = Isnull(@.SearchPattern,'') + @.Pattern;
Select @.Len = @.Len -1;
End
Select @.Result = Case When @.Value Like @.SearchPattern Then 1 Else 0 End;
Return @.Result;
End
Go
select dbo.IsMatching('SQLServer','[A-Z]') as Result
Result : 1
select dbo.IsMatching('SQL Server','[A-Z]') as Result
Result : 0 --Space on String
select dbo.IsMatching('SQL Server','[A-Z ]') as Result
Result : 1 --Space added on Pattern
select dbo.IsMatching('12453','[1-5]') as Result
Result : 1
select dbo.IsMatching('12463','[1-5]') as Result
Result : 0
|||thanks mani,
got the [A-Z] and [^a-z] concept.
my other requirements are to match zero or more characters and to match one or more characters.
e.g. T*he should match he, the, tthe, ttttthe.. etc.
and t+ho should match tho, thho, thhhhhhho, thhhhhhhhhhhhhho.. etc.
the above operators i have used in VC++,
do they work in t-sql too?
|||If you need regular expression and your platform is sqlserver 2005 you can use a CLR strored procedure. If you need help on this post a question on the .net framework inside sql server forum
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=86&SiteID=1|||
Yes.. if you want to find the repeated chars you can use the following condtions....
columnname not like '%aaa%'
and columnname not like '%bbb%'
and columnname not like '%ccc%'
and columnname not like '%ddd%'
and columnname not like '%eee%'
and columnname not like '%fff%'
and columnname not like '%ggg%'
and columnname not like '%hhh%'
and columnname not like '%iiii%'
and columnname not like '%jjj%'
and columnname not like '%kkk%'
and columnname not like '%lll%'
and columnname not like '%mmm%'
and columnname not like '%nnn%'
and columnname not like '%ooo%'
and columnname not like '%ppp%'
and columnname not like '%qqq%'
and columnname not like '%rrr%'
and columnname not like '%sss%'
and columnname not like '%ttt%'
and columnname not like '%uuu%'
and columnname not like '%vvv%'
and columnname not like '%www%'
and columnname not like '%xxx%'
and columnname not like '%yyy%'
and columnname not like '%zzz%'
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=996813&SiteID=1
If you want to utilize the exact regular expression as i said earlier you can go for CLR Functions..
Regular expressions in Sql Server?
Hi,
I'm using MS SQL Server 2000 and am trying to execute a select where I want a column to match this regular expression:
((\w)*\|)*$KEY(\|(\w)*)*
as would be defined in Perl (btw, the "|" is a pipe, not an L :))
$KEY would be subsituted by some text I'm querying for.
I tried using LIKE with "[(%|)[]]$KEY[[](|%)]", eg:
select * from mytable m
where m.a like '[(%|)[]]$KEY[[](|%)]'
but it didn't work (again, the $KEY is replaced with text when I'm executing. I just have it here for an example). Is there any way I could get around this problem? I would really appreciate any help.
Thanksthe best you will do is "%|$Key|%", ie, a string that starts with something, has a pipe char, the string you are looking for, a pipe char, ending with something.
I believe "\|" in perl is how you specify a literal "|".
Regular expressions are not supported in TSQL! There are some simple expressions but not what a perl or python programmer is used to.
I'm using MS SQL Server 2000 and am trying to execute a select where I want a column to match this regular expression:
((\w)*\|)*$KEY(\|(\w)*)*
as would be defined in Perl (btw, the "|" is a pipe, not an L :))
$KEY would be subsituted by some text I'm querying for.
I tried using LIKE with "[(%|)[]]$KEY[[](|%)]", eg:
select * from mytable m
where m.a like '[(%|)[]]$KEY[[](|%)]'
but it didn't work (again, the $KEY is replaced with text when I'm executing. I just have it here for an example). Is there any way I could get around this problem? I would really appreciate any help.
Thanksthe best you will do is "%|$Key|%", ie, a string that starts with something, has a pipe char, the string you are looking for, a pipe char, ending with something.
I believe "\|" in perl is how you specify a literal "|".
Regular expressions are not supported in TSQL! There are some simple expressions but not what a perl or python programmer is used to.
Regular Expressions in SQL Server 2005
Hi-
Someone posted on a thread that you can use VB (or C#) to write Regular Expressions for SQL Server 2005. I know how to write RegEx in VB, so can someone point me to a reference which explains how to use them in / with SQL2005? Or, can someone explain how?
Thanks,
-David
OK. At this point I'm pretty sure that this can be accomplished through the common language runtime. Can someone confirm this?
-David
|||Yes with CLR intergration in SQL Server 2005, you can write CLR Stored procedures in VB or C#. By referring to System.Text.RegularExpressions namespace you can add regular expression support to your stored procs. Here are couple of links to get you started:
http://msdn2.microsoft.com/en-us/library/ms131094(SQL.90).aspx
http://www.dotnetfun.com/articles/sql/sql2005/SQL2005CLRSProc.aspx
|||Thanks for the links. I've been using regex in my VBA project, and MS seems to have done a pretty decent job implementing it. I'm a PERL regex expert from long before which of course helps.
Thanks again.
Regular Expressions in SQL 2005 using C#
Hi all,
i wrote a little function that is basically supposed to give me a match of a regular expression in C#. i tested it out in VS05 and it seems to work fine. It hits the fan when i try it on SQL Query analyzer once deployed. Anyone have any idea why?
here is the C# class that I deploy to a db using VS05.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
public partial class RegExTest
{
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static string RegExMatch(string pattern, string matchString)
{
Regex r1 = new Regex(pattern);
if (r1.Match(matchString.TrimEnd(null)).Success)
{
Regex testReg = new Regex(pattern);
Match regMatch = testReg.Match(matchString);
string runnumber = regMatch.ToString().TrimEnd('_');
runnumber = runnumber.TrimStart('_');
return runnumber;
}
else
{
return null;
}
}
};
Basically i have a teststring that looks like abc_xxx_nnndef. xxx and nn are both numbers, and im using the pattern _\\d{3}_ to test it out in SQL (i need the numbers between '_' and '_'). But it never even proceeds past the if statement in SQL i think. here is the basic SQL that i use to test the above function:
use northwind
go
declare @.teststring varchar(50),
@.pattern varchar(10),
@.out varchar(50)
set @.teststring = 'dw_012_34.ext'
set @.pattern = '_\\d{3}_'
set @.out = dbo.RegExMatch(@.pattern, @.teststring )
select @.teststring
select @.pattern
select @.out
Anyone have any suggestions as to why it works in VS 05 when I use it in a C# program, but fails on the SQL version?
Hi,
without escaping the string this, worked for me, the pattern therefore was _\d{3}_ not this with the double backslash.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Actually, I had made a silly mistake testing my code. The above code works just fine. Sorry for the inconvenience!
|||i am getting the error while calling
dbo.RegExMatch(@.pattern, @.teststring )
this line in stored procedure,
i have already created the assembly.
still i am getting the error!
|||Which error ?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
the scene is this:
after creating an assembly name Reg_ex,
i need to call the method regmatch which is in Reg_ex, in class regtest.
when i say"
CREATE PROCEDURE RegExMatch
AS external name Reg_Ex.RegTest.RegExMatch
error is:
Msg 6505, Level 16, State 1, Procedure RegExMatch, Line 1
Could not find Type 'RegTest' in assembly 'Reg_Ex'.
Regular Expressions in SQL 2005 using C#
Hi all,
i wrote a little function that is basically supposed to give me a match of a regular expression in C#. i tested it out in VS05 and it seems to work fine. It hits the fan when i try it on SQL Query analyzer once deployed. Anyone have any idea why?
here is the C# class that I deploy to a db using VS05.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
public partial class RegExTest
{
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static string RegExMatch(string pattern, string matchString)
{
Regex r1 = new Regex(pattern);
if (r1.Match(matchString.TrimEnd(null)).Success)
{
Regex testReg = new Regex(pattern);
Match regMatch = testReg.Match(matchString);
string runnumber = regMatch.ToString().TrimEnd('_');
runnumber = runnumber.TrimStart('_');
return runnumber;
}
else
{
return null;
}
}
};
Basically i have a teststring that looks like abc_xxx_nnndef. xxx and nn are both numbers, and im using the pattern _\\d{3}_ to test it out in SQL (i need the numbers between '_' and '_'). But it never even proceeds past the if statement in SQL i think. here is the basic SQL that i use to test the above function:
use northwind
go
declare @.teststring varchar(50),
@.pattern varchar(10),
@.out varchar(50)
set @.teststring = 'dw_012_34.ext'
set @.pattern = '_\\d{3}_'
set @.out = dbo.RegExMatch(@.pattern, @.teststring )
select @.teststring
select @.pattern
select @.out
Anyone have any suggestions as to why it works in VS 05 when I use it in a C# program, but fails on the SQL version?
Hi,
without escaping the string this, worked for me, the pattern therefore was _\d{3}_ not this with the double backslash.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Actually, I had made a silly mistake testing my code. The above code works just fine. Sorry for the inconvenience!
|||i am getting the error while calling
dbo.RegExMatch(@.pattern, @.teststring )
this line in stored procedure,
i have already created the assembly.
still i am getting the error!
|||Which error ?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
the scene is this:
after creating an assembly name Reg_ex,
i need to call the method regmatch which is in Reg_ex, in class regtest.
when i say"
CREATE PROCEDURE RegExMatch
AS external name Reg_Ex.RegTest.RegExMatch
error is:
Msg 6505, Level 16, State 1, Procedure RegExMatch, Line 1
Could not find Type 'RegTest' in assembly 'Reg_Ex'.
Regular Expression?
I need to remove all the [ and ] in every sql stmt where they are used
to close encircling numeric value, fyi, all these sql stmt are
converted from an Access db. For instance,
select *
from XYZtbl
where fieldA = [1] or fieldA = [2] or fieldA = [3]
to close encircling numeric value, fyi, all these sql stmt are
converted from an Access db. For instance,
select *
from XYZtbl
where fieldA = [1] or fieldA = [2] or fieldA = [3]
to be
select *
from XYZtbl
where fieldA = 1 or fieldA = 2 or fieldA = 3
-- and of course I'd have written in as follows, but that's not
-- the point
select *
from XYZtbl
where fieldA IN (1,2,3)
Thanks.Hi
If you are generating these queries dynamically they should be removed in
the code that generates them.
You can not change the query once it has been sent to the server unless you
write your own driver, therefore you will have to change the source. You may
want to create stored procedures from these queries rather than use ad-hoc
queries.
John
"NickName" <dadada@.rock.com> wrote in message
news:1125970738.466448.247370@.g14g2000cwa.googlegr oups.com...
> I need to remove all the [ and ] in every sql stmt where they are used
> to close encircling numeric value, fyi, all these sql stmt are
> converted from an Access db. For instance,
> select *
> from XYZtbl
> where fieldA = [1] or fieldA = [2] or fieldA = [3]
> to be
> select *
> from XYZtbl
> where fieldA = 1 or fieldA = 2 or fieldA = 3
> -- and of course I'd have written in as follows, but that's not
> -- the point
> select *
> from XYZtbl
> where fieldA IN (1,2,3)
> Thanks.|||On 5 Sep 2005 18:38:58 -0700, NickName wrote:
>I need to remove all the [ and ] in every sql stmt where they are used
>to close encircling numeric value, fyi, all these sql stmt are
>converted from an Access db. For instance,
>select *
>from XYZtbl
>where fieldA = [1] or fieldA = [2] or fieldA = [3]
>to be
>select *
>from XYZtbl
>where fieldA = 1 or fieldA = 2 or fieldA = 3
>-- and of course I'd have written in as follows, but that's not
>-- the point
>select *
>from XYZtbl
>where fieldA IN (1,2,3)
>Thanks.
Hi NickName,
Where are these queries stored?
SQL Server doesn't store actual queries - it accepts them, executes
them, then returns you the results.
On the other hand, the code for stored procedures, user-defined
functions and triggers is stored in SQL Server.
If you have your queries stored in files on your computer, check
www.tucows.com for a freeware or shareware GREP-like utility to quickly
make the change for you (but beware that you specify the regular
expression patterns in a way to prevent unwanted side-effects!!)
If this is code in stored procedures etc, then there is no easy way to
change it all at once. You'll either have to change the procedures one
by one (which is a good opportunity to bring them all in some change
control system), or investigate what went wrong on the conversion from
Access, fix that problem, then re-convert.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,
These are 'saved' queries in Access I'm in the process of converting
them to either View or SP, and obviously SQL Server would accept
something like
select *
from XYZtbl
where fieldA = [1] or fieldA = [2] or fieldA = [3]
I thought about GREP but wanted to check if there's other option.
On Access conversion, it convert queries to virtual tables.
Thanks.
Don|||"If you are generating these queries dynamically they should be removed
in
the code that generates them."
Exactly, that's what I wanted to do in the generation/conversion
process.
"You may
want to create stored procedures from these queries rather than use
ad-hoc
queries. "
Yes, either view or sp.
Thanks.|||Hi
Then you should look at the search/replace functionality of a text editor or
even Query Analyser.
I have not come across this thing before in access queries, so I am not sure
why they are there.
John
"NickName" <dadada@.rock.com> wrote in message
news:1126045915.321908.319130@.g49g2000cwa.googlegr oups.com...
> "If you are generating these queries dynamically they should be removed
> in
> the code that generates them."
> Exactly, that's what I wanted to do in the generation/conversion
> process.
> "You may
> want to create stored procedures from these queries rather than use
> ad-hoc
> queries. "
> Yes, either view or sp.
> Thanks.
Regular expression..
Does SqlServer support Regular expression?
I want to know if it's avilable or not...
And if it's not available, I want to know any other efficient way to validate string.. manipulating Regular expression style validation.
Thank you all...use sp_oaxxx, works like a charm :)|||Transact-SQL 2000 supports a very crude, grep like set of regular expressions for the LIKE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_la-lz_115x.asp) operator. I don't know of anything like the regular expressions in Perl, awk, vi, or gres within Transact-SQL (and would often kill for such a thing!). This is coming in Yukon, but that is still a ways off.
-PatP|||Pat, check this (http://www.sqlteam.com/item.asp?ItemID=13947) out, it may change your mind ;)|||That example gets you about 30% of grep, which is a considerable improvement over what LIKE gives you. It doesn't get any significant part of regexp as offered by Perl, awk, gres, etc.
I still use Perl when the going gets tough. Perl can open an ODBC connection, mangle the data, and be done before I could figure out how to identify the data that I needed to change using LIKE!
-PatP|||No pressure, just a thought ;)|||Thank you all for considering.
rdjabarov's link give that I really needed.
I want to know if it's avilable or not...
And if it's not available, I want to know any other efficient way to validate string.. manipulating Regular expression style validation.
Thank you all...use sp_oaxxx, works like a charm :)|||Transact-SQL 2000 supports a very crude, grep like set of regular expressions for the LIKE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_la-lz_115x.asp) operator. I don't know of anything like the regular expressions in Perl, awk, vi, or gres within Transact-SQL (and would often kill for such a thing!). This is coming in Yukon, but that is still a ways off.
-PatP|||Pat, check this (http://www.sqlteam.com/item.asp?ItemID=13947) out, it may change your mind ;)|||That example gets you about 30% of grep, which is a considerable improvement over what LIKE gives you. It doesn't get any significant part of regexp as offered by Perl, awk, gres, etc.
I still use Perl when the going gets tough. Perl can open an ODBC connection, mangle the data, and be done before I could figure out how to identify the data that I needed to change using LIKE!
-PatP|||No pressure, just a thought ;)|||Thank you all for considering.
rdjabarov's link give that I really needed.
Regular Expression Support
Is there any Reg Ex support in any recent version of SQL Server; like for
use with check constraints?
-ThanksYou can create user-defined function in a CLR language like C#. In this you
can use the RegEx libraries and then expose that CLR function to SQL Server
2005. In fact there is an example of this in the books online.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Jeff S" <A@.B.COM> wrote in message
news:uwGACL5MGHA.2916@.tk2msftngp13.phx.gbl...
> Is there any Reg Ex support in any recent version of SQL Server; like for
> use with check constraints?
> -Thanks
>
use with check constraints?
-ThanksYou can create user-defined function in a CLR language like C#. In this you
can use the RegEx libraries and then expose that CLR function to SQL Server
2005. In fact there is an example of this in the books online.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Jeff S" <A@.B.COM> wrote in message
news:uwGACL5MGHA.2916@.tk2msftngp13.phx.gbl...
> Is there any Reg Ex support in any recent version of SQL Server; like for
> use with check constraints?
> -Thanks
>
regular expression issue
I am trying to exclude all strings that has 'a' inside (I have
simplified the actual problem)
select 1
where 'bb b a dfg' like '%[^a]%'
simplified the actual problem)
select 1
where 'bb b a dfg' like '%[^a]%'
However, the above does not work. By the way, I can not use 'not like'
such as:
select 1
where 'bb b a dfg' not like '%a%'
Although the above will work but the idea is that I have to use 'like'
and not 'not like'. This is partly because I have to exclude rows from
an exclusion table (a table that has many rows that will be excluded).
Actually I want to include all srings that has lets say // in it using
a regular expression. I would like to write it as (I am sure it will
not work):
select 1
where column like '%[^/][^/]%'
That should exclude strings like: 'aaa // aa aa' or 'bb bbb // bb' etc
and include strings like: 'aaa aa aa' or 'bb aa nn' etc
Is there any way to write a regular expression to do it? Otherrwise I
have to solve this problem without using regular expressions in the
exclusion table.
Thanks.The problem you pose requires NOT LIKE. There is no way to express a
LIKE string with the "not" test inside that will do what was
specified. The NOT has to be outside.
Roy Harvey
Beacon Falls, CT
On 7 Nov 2006 21:07:01 -0800, othellomy@.yahoo.com wrote:
Quote:
Originally Posted by
>I am trying to exclude all strings that has 'a' inside (I have
>simplified the actual problem)
>select 1
>where 'bb b a dfg' like '%[^a]%'
>
>However, the above does not work. By the way, I can not use 'not like'
>such as:
>
>select 1
>where 'bb b a dfg' not like '%a%'
>Although the above will work but the idea is that I have to use 'like'
>and not 'not like'. This is partly because I have to exclude rows from
>an exclusion table (a table that has many rows that will be excluded).
>Actually I want to include all srings that has lets say // in it using
>a regular expression. I would like to write it as (I am sure it will
>not work):
>select 1
>where column like '%[^/][^/]%'
>
>That should exclude strings like: 'aaa // aa aa' or 'bb bbb // bb' etc
>and include strings like: 'aaa aa aa' or 'bb aa nn' etc
>
>Is there any way to write a regular expression to do it? Otherrwise I
>have to solve this problem without using regular expressions in the
>exclusion table.
>Thanks.
Regular expression in SQL 2000
Hi All,
We are migrating a web application from oracle to SQL Server 2000.We have
used regular expression extensively in Oracle.
Is it possible to use Regular expressions in SQL 2000 queries'
GuhanCheck out the LIKE operator and the PATINDEX function. It isn't the full
regex syntax but basic wildcards and search patterns are supported.
David Portas
SQL Server MVP
--|||Can you give more details on what you are using. As David said in SQL 2K you
always have PATINDEX to help. Yet not complete in the capability. Just
wanted to add that in SQL Server 2005 you can use the capability of CLR to
perform
regex work.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Guhanath" <Guhanath@.discussions.microsoft.com> wrote in message
news:D2295C71-1483-4D89-B24C-5550FA00D7CE@.microsoft.com...
> Hi All,
> We are migrating a web application from oracle to SQL Server 2000.We
have
> used regular expression extensively in Oracle.
> Is it possible to use Regular expressions in SQL 2000 queries'
>
> --
> Guhan|||Have a look at
http://blogs.msdn.com/khen1234/arch.../11/416392.aspx
http://blogs.msdn.com/khen1234/arch.../19/420209.aspx
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
"Guhanath" <Guhanath@.discussions.microsoft.com> wrote in message
news:D2295C71-1483-4D89-B24C-5550FA00D7CE@.microsoft.com...
> Hi All,
> We are migrating a web application from oracle to SQL Server 2000.We
> have
> used regular expression extensively in Oracle.
> Is it possible to use Regular expressions in SQL 2000 queries'
>
> --
> Guhan
We are migrating a web application from oracle to SQL Server 2000.We have
used regular expression extensively in Oracle.
Is it possible to use Regular expressions in SQL 2000 queries'
GuhanCheck out the LIKE operator and the PATINDEX function. It isn't the full
regex syntax but basic wildcards and search patterns are supported.
David Portas
SQL Server MVP
--|||Can you give more details on what you are using. As David said in SQL 2K you
always have PATINDEX to help. Yet not complete in the capability. Just
wanted to add that in SQL Server 2005 you can use the capability of CLR to
perform
regex work.HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Guhanath" <Guhanath@.discussions.microsoft.com> wrote in message
news:D2295C71-1483-4D89-B24C-5550FA00D7CE@.microsoft.com...
> Hi All,
> We are migrating a web application from oracle to SQL Server 2000.We
have
> used regular expression extensively in Oracle.
> Is it possible to use Regular expressions in SQL 2000 queries'
>
> --
> Guhan|||Have a look at
http://blogs.msdn.com/khen1234/arch.../11/416392.aspx
http://blogs.msdn.com/khen1234/arch.../19/420209.aspx
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
"Guhanath" <Guhanath@.discussions.microsoft.com> wrote in message
news:D2295C71-1483-4D89-B24C-5550FA00D7CE@.microsoft.com...
> Hi All,
> We are migrating a web application from oracle to SQL Server 2000.We
> have
> used regular expression extensively in Oracle.
> Is it possible to use Regular expressions in SQL 2000 queries'
>
> --
> Guhan
regular expression
Hi all,
I m able to use regular expression in a Vbs script, but how can I use them
(if it's possible ) in reporting service code ?
I havent found any subjects like this in the my books (even in "MRS in
action" by Téo Lachev :) )
ThanksFirst, add a function to do your Regular expression in the report level
Code (Report --> Properties --> Code) . Here's an example:
----
Public Function RegExTest () as Object
Dim Str As String = "<expressiontext>the text the returned by
the regular expression</expressiontext>"
Dim expressiontext As String
Dim regexexpressiontext As System.Text.RegularExpressions.Regex
= New
System.Text.RegularExpressions.Regex("<expressiontext>(?<expressiontext>[^<]+)</expressiontext>",
System.Text.RegularExpressions.RegexOptions.IgnoreCase)
expressiontext =regexexpressiontext.Match(Str).Groups("expressiontext").Value
RegExTest = expressiontext
End Function
----
Now your text box will refer to the code in a fashion similar to this:
=Code.RegExTest
And low and behold, regular expressions in Reporting Services.
Andy Potter|||OK.
Thanks a lot !
"Potter" wrote:
> First, add a function to do your Regular expression in the report level
> Code (Report --> Properties --> Code) . Here's an example:
> ----
> Public Function RegExTest () as Object
> Dim Str As String = "<expressiontext>the text the returned by
> the regular expression</expressiontext>"
> Dim expressiontext As String
> Dim regexexpressiontext As System.Text.RegularExpressions.Regex
> = New
> System.Text.RegularExpressions.Regex("<expressiontext>(?<expressiontext>[^<]+)</expressiontext>",
> System.Text.RegularExpressions.RegexOptions.IgnoreCase)
> expressiontext => regexexpressiontext.Match(Str).Groups("expressiontext").Value
> RegExTest = expressiontext
> End Function
> ----
> Now your text box will refer to the code in a fashion similar to this:
> =Code.RegExTest
> And low and behold, regular expressions in Reporting Services.
> Andy Potter
>
I m able to use regular expression in a Vbs script, but how can I use them
(if it's possible ) in reporting service code ?
I havent found any subjects like this in the my books (even in "MRS in
action" by Téo Lachev :) )
ThanksFirst, add a function to do your Regular expression in the report level
Code (Report --> Properties --> Code) . Here's an example:
----
Public Function RegExTest () as Object
Dim Str As String = "<expressiontext>the text the returned by
the regular expression</expressiontext>"
Dim expressiontext As String
Dim regexexpressiontext As System.Text.RegularExpressions.Regex
= New
System.Text.RegularExpressions.Regex("<expressiontext>(?<expressiontext>[^<]+)</expressiontext>",
System.Text.RegularExpressions.RegexOptions.IgnoreCase)
expressiontext =regexexpressiontext.Match(Str).Groups("expressiontext").Value
RegExTest = expressiontext
End Function
----
Now your text box will refer to the code in a fashion similar to this:
=Code.RegExTest
And low and behold, regular expressions in Reporting Services.
Andy Potter|||OK.
Thanks a lot !
"Potter" wrote:
> First, add a function to do your Regular expression in the report level
> Code (Report --> Properties --> Code) . Here's an example:
> ----
> Public Function RegExTest () as Object
> Dim Str As String = "<expressiontext>the text the returned by
> the regular expression</expressiontext>"
> Dim expressiontext As String
> Dim regexexpressiontext As System.Text.RegularExpressions.Regex
> = New
> System.Text.RegularExpressions.Regex("<expressiontext>(?<expressiontext>[^<]+)</expressiontext>",
> System.Text.RegularExpressions.RegexOptions.IgnoreCase)
> expressiontext => regexexpressiontext.Match(Str).Groups("expressiontext").Value
> RegExTest = expressiontext
> End Function
> ----
> Now your text box will refer to the code in a fashion similar to this:
> =Code.RegExTest
> And low and behold, regular expressions in Reporting Services.
> Andy Potter
>
Regular Disconnection occurs in query analyzer??
Hi,
today we have a strange case, my developpers are disconnected from the
server under query analyzer only from remote stations.
The user must refresh the connection at the higher level to be connected
again.
but every 10 to 15 minutes my users are disconnected again!!!
From what I can see, this event occurs for all clients at the same time.
Any idea?
I'm connected through Terminal server to my server to see if there is any
problem, and I don't see anything wrong.
There is nothing in the event log and nothing in the SQL Server log.
Thanks for your help.
Jerome.
> From: "Jj" <willgart@._A_hAotmail_A_.com>
> Subject: Regular Disconnection occurs in query analyzer?
> Date: Tue, 27 Apr 2004 15:29:43 -0400
> Hi,
> today we have a strange case, my developpers are disconnected from the
> server under query analyzer only from remote stations.
> The user must refresh the connection at the higher level to be connected
> again.
> but every 10 to 15 minutes my users are disconnected again!!!
> From what I can see, this event occurs for all clients at the same time.
> Any idea?
> I'm connected through Terminal server to my server to see if there is any
> problem, and I don't see anything wrong.
> There is nothing in the event log and nothing in the SQL Server log.
> Thanks for your help.
> Jerome.
Hi Jerome,
Check the SQL Server errorlogs for any errors coinciding with the
disconnects. If nothing, you may have to capture a network trace to find
out why users are disconnecting.
Hope this helps,
Eric Crdenas
SQL Server senior support professional
today we have a strange case, my developpers are disconnected from the
server under query analyzer only from remote stations.
The user must refresh the connection at the higher level to be connected
again.
but every 10 to 15 minutes my users are disconnected again!!!
From what I can see, this event occurs for all clients at the same time.
Any idea?
I'm connected through Terminal server to my server to see if there is any
problem, and I don't see anything wrong.
There is nothing in the event log and nothing in the SQL Server log.
Thanks for your help.
Jerome.
> From: "Jj" <willgart@._A_hAotmail_A_.com>
> Subject: Regular Disconnection occurs in query analyzer?
> Date: Tue, 27 Apr 2004 15:29:43 -0400
> Hi,
> today we have a strange case, my developpers are disconnected from the
> server under query analyzer only from remote stations.
> The user must refresh the connection at the higher level to be connected
> again.
> but every 10 to 15 minutes my users are disconnected again!!!
> From what I can see, this event occurs for all clients at the same time.
> Any idea?
> I'm connected through Terminal server to my server to see if there is any
> problem, and I don't see anything wrong.
> There is nothing in the event log and nothing in the SQL Server log.
> Thanks for your help.
> Jerome.
Hi Jerome,
Check the SQL Server errorlogs for any errors coinciding with the
disconnects. If nothing, you may have to capture a network trace to find
out why users are disconnecting.
Hope this helps,
Eric Crdenas
SQL Server senior support professional
Regular Disconnection occurs in query analyzer??
Hi,
today we have a strange case, my developpers are disconnected from the
server under query analyzer only from remote stations.
The user must refresh the connection at the higher level to be connected
again.
but every 10 to 15 minutes my users are disconnected again!!!
From what I can see, this event occurs for all clients at the same time.
Any idea?
I'm connected through Terminal server to my server to see if there is any
problem, and I don't see anything wrong.
There is nothing in the event log and nothing in the SQL Server log.
Thanks for your help.
Jerome.> From: "Jj" <willgart@._A_hAotmail_A_.com>
> Subject: Regular Disconnection occurs in query analyzer?
> Date: Tue, 27 Apr 2004 15:29:43 -0400
> Hi,
> today we have a strange case, my developpers are disconnected from the
> server under query analyzer only from remote stations.
> The user must refresh the connection at the higher level to be connected
> again.
> but every 10 to 15 minutes my users are disconnected again!!!
> From what I can see, this event occurs for all clients at the same time.
> Any idea?
> I'm connected through Terminal server to my server to see if there is any
> problem, and I don't see anything wrong.
> There is nothing in the event log and nothing in the SQL Server log.
> Thanks for your help.
> Jerome.
--
Hi Jerome,
Check the SQL Server errorlogs for any errors coinciding with the
disconnects. If nothing, you may have to capture a network trace to find
out why users are disconnecting.
Hope this helps,
Eric Crdenas
SQL Server senior support professional
today we have a strange case, my developpers are disconnected from the
server under query analyzer only from remote stations.
The user must refresh the connection at the higher level to be connected
again.
but every 10 to 15 minutes my users are disconnected again!!!
From what I can see, this event occurs for all clients at the same time.
Any idea?
I'm connected through Terminal server to my server to see if there is any
problem, and I don't see anything wrong.
There is nothing in the event log and nothing in the SQL Server log.
Thanks for your help.
Jerome.> From: "Jj" <willgart@._A_hAotmail_A_.com>
> Subject: Regular Disconnection occurs in query analyzer?
> Date: Tue, 27 Apr 2004 15:29:43 -0400
> Hi,
> today we have a strange case, my developpers are disconnected from the
> server under query analyzer only from remote stations.
> The user must refresh the connection at the higher level to be connected
> again.
> but every 10 to 15 minutes my users are disconnected again!!!
> From what I can see, this event occurs for all clients at the same time.
> Any idea?
> I'm connected through Terminal server to my server to see if there is any
> problem, and I don't see anything wrong.
> There is nothing in the event log and nothing in the SQL Server log.
> Thanks for your help.
> Jerome.
--
Hi Jerome,
Check the SQL Server errorlogs for any errors coinciding with the
disconnects. If nothing, you may have to capture a network trace to find
out why users are disconnecting.
Hope this helps,
Eric Crdenas
SQL Server senior support professional
Wednesday, March 7, 2012
Regular Disconnection occurs in query analyzer??
Hi,
today we have a strange case, my developpers are disconnected from the
server under query analyzer only from remote stations.
The user must refresh the connection at the higher level to be connected
again.
but every 10 to 15 minutes my users are disconnected again!!!
From what I can see, this event occurs for all clients at the same time.
Any idea?
I'm connected through Terminal server to my server to see if there is any
problem, and I don't see anything wrong.
There is nothing in the event log and nothing in the SQL Server log.
Thanks for your help.
Jerome.> From: "Jéjé" <willgart@._A_hAotmail_A_.com>
> Subject: Regular Disconnection occurs in query analyzer?
> Date: Tue, 27 Apr 2004 15:29:43 -0400
> Hi,
> today we have a strange case, my developpers are disconnected from the
> server under query analyzer only from remote stations.
> The user must refresh the connection at the higher level to be connected
> again.
> but every 10 to 15 minutes my users are disconnected again!!!
> From what I can see, this event occurs for all clients at the same time.
> Any idea?
> I'm connected through Terminal server to my server to see if there is any
> problem, and I don't see anything wrong.
> There is nothing in the event log and nothing in the SQL Server log.
> Thanks for your help.
> Jerome.
--
Hi Jerome,
Check the SQL Server errorlogs for any errors coinciding with the
disconnects. If nothing, you may have to capture a network trace to find
out why users are disconnecting.
Hope this helps,
--
Eric Cárdenas
SQL Server senior support professional
today we have a strange case, my developpers are disconnected from the
server under query analyzer only from remote stations.
The user must refresh the connection at the higher level to be connected
again.
but every 10 to 15 minutes my users are disconnected again!!!
From what I can see, this event occurs for all clients at the same time.
Any idea?
I'm connected through Terminal server to my server to see if there is any
problem, and I don't see anything wrong.
There is nothing in the event log and nothing in the SQL Server log.
Thanks for your help.
Jerome.> From: "Jéjé" <willgart@._A_hAotmail_A_.com>
> Subject: Regular Disconnection occurs in query analyzer?
> Date: Tue, 27 Apr 2004 15:29:43 -0400
> Hi,
> today we have a strange case, my developpers are disconnected from the
> server under query analyzer only from remote stations.
> The user must refresh the connection at the higher level to be connected
> again.
> but every 10 to 15 minutes my users are disconnected again!!!
> From what I can see, this event occurs for all clients at the same time.
> Any idea?
> I'm connected through Terminal server to my server to see if there is any
> problem, and I don't see anything wrong.
> There is nothing in the event log and nothing in the SQL Server log.
> Thanks for your help.
> Jerome.
--
Hi Jerome,
Check the SQL Server errorlogs for any errors coinciding with the
disconnects. If nothing, you may have to capture a network trace to find
out why users are disconnecting.
Hope this helps,
--
Eric Cárdenas
SQL Server senior support professional
regular backups
Hi,
Is it possible to make regular back ups of a database in such fashion that
it is a bit like a version control system.. where you store differences
(incremental?) but in such way that backups are made daily or weekly and you
can decide to restore a backup from 20 days ago?
In other words, a 'history' of backups should be kept so I can go back 5
backup sessions ago and restore something that's 5 days old. If this is
possible, how can I pick the 'date' from which to backup from?
Using SQL Server 8/2000
Lisa
Lisa,
have a look at creating a maintenance plan. You can select to keep x backups
made at whatever frequency you require and it'll round-robin the files. The
backups have a filename indicating their backup time. SQL Server doesn't do
incremental backups (not even in SQL 2005 AFAIR), but does differential and
transaction log backups which are covered in books-on-line (BOL) and might
be what you require.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Is it possible to make regular back ups of a database in such fashion that
it is a bit like a version control system.. where you store differences
(incremental?) but in such way that backups are made daily or weekly and you
can decide to restore a backup from 20 days ago?
In other words, a 'history' of backups should be kept so I can go back 5
backup sessions ago and restore something that's 5 days old. If this is
possible, how can I pick the 'date' from which to backup from?
Using SQL Server 8/2000
Lisa
Lisa,
have a look at creating a maintenance plan. You can select to keep x backups
made at whatever frequency you require and it'll round-robin the files. The
backups have a filename indicating their backup time. SQL Server doesn't do
incremental backups (not even in SQL 2005 AFAIR), but does differential and
transaction log backups which are covered in books-on-line (BOL) and might
be what you require.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Regular Backup co-exist with log-shipping (or SQL mirroring)
Suppose I have a 3rd party SQL backup software to backup my databases, can I also setup a stand-by server for log shipping?
I'm not sure if this is possible? If not, is SQL mirroring a choice instead of log shipping?
Thanks a lot!
The only restriction is that only one thread of log backups can exist.
So, if your 3rd-party backup software is also doing log backups, log shipping will not work.
Alternatively, you can do full backups with your 3rd-party software and log shipping will work just fine.
Mirroring has no such restrictions.
Regular Backup co-exist with log-shipping (or SQL mirroring)
Suppose I have a 3rd party SQL backup software to backup my databases, can I also setup a stand-by server for log shipping?
I'm not sure if this is possible? If not, is SQL mirroring a choice instead of log shipping?
Thanks a lot!
The only restriction is that only one thread of log backups can exist.
So, if your 3rd-party backup software is also doing log backups, log shipping will not work.
Alternatively, you can do full backups with your 3rd-party software and log shipping will work just fine.
Mirroring has no such restrictions.
Subscribe to:
Posts (Atom)