SQL 2005 Cluster Behavior - Re Authenticate After Moving Resources...
Oct 30, 2007
On a SQL 2005 Cluster, when you move the SQL resources you generally have to kind of "reauthenticate". For example, in SSMS and after you have moved the SQL cluster resources, you run a query in SSMS and you get a connection failure. Run it again and it works.
Does anyone else notice this? I am assuming it is because the resources are now on a differen't server. But then what's the purpose of the SQL virtual server name and instance? I thought when you authenticate to SQL it's to the virtual SQL server like so... VirtualServerSQLInstance.
Can someone straighten out my poor mangled mind?
I'd like to avoid this reauthentication thing.
Hi. I have installed an SQL 2005 Failover Cluster on a Two Node Active Passive Windows 2003 Cluster.If i am trying to failover from the Active node to the passive Node,The Sql Server,Sql Server Agent and SQL Analysis resources fail,However if i reset the passowrd in the services tab of the above three services on the pasive node,the resources come online. Below is the brief of my setup 1.I have two Active directory domain controllers running Windows 2003 R2 Standard edition with SP2. 2.i have installed a Windows 2003 Two Node Active Passive Cluster as NODE1 and NODE2. 3.The Domain account used to install WIndows 2003 A/P Cluster is Clusteradmin.This account is the member of Administartors on the Domain as well as the Local Admin on NODE1 and NODE2. 4.SQL 2005 with SP2 is installed on both the Nodes as SQL 2005 Failover Cluster.The account used to install SQL 2005 is sqadmin.This account is the member of Administartors on the domain and the member of Local Admins on NODE1 and NODE2. 5.SLQ 2005 has four domain groups for 4 SQL Services.The Services are SQL Server,SQL Server Agent,SLQ Anaylisis Server and Full text Search. 6.Each of these servcie has a seperate service account created for them.All these service accounts areb the members of domain admin and the member of Local Admin on NODE1 and NODE2. 7.Each of these servcies is running under these servcie accounts in the Servcies tab in NODE1 and NODE2. 8.If i fail the resources from NODE1 to NODE2 ,The SQL Server,SQL Server Agent and SQL Anaylisis resources are failing.on going to the service tab of NODE 2 I reset the password for these services,the services come online. 10.The Cluster resources and MSDTC Resources are Failing over successfully.They are coming online successfully. 11,I have a problem with the SQL Server Resources,even if i failback to NODE1 from NODE2,the same resources are failing again.
Is there a way for multiple SQL 2005 instances in a cluster to share drives for user databases.
Once we install our instances in the cluster using seperate disk resources. Can we then have multiple instances share the same physical disk for user databases only. This is for a test environment.
I just installed SQL reporting Services 2005 on my XP Pro SP1 machine. The problem is when I try to login to http://localhost/Reports or http://localhost/reportserver using "Administrator" account it keeps prompting me the same windows login dialogbox it does not authenticate. I have tried to log in as other user too but same problem.
Can someone please guide me what could be wrong with my setup.
Tried SA.. Tried AD Admin, tried local administrator. Set up in mixed mode so i tried windows and sql authen... I can log in normally with SA and the password and the windows admin account.
Nothing works, tried services off and on. Tried clearing everything
Reinstalled completely...
Only the SQL Server part fails because it requires authentication. All the other parts succeed.
I don't get a log since it just thinks im typing things wrong. Just this
06/06/2006 21:24:59.641 Authenticating user using Windows Authentication 06/06/2006 21:24:59.641 SQL Service MSSQLServer was previously running, ready for authentication 06/06/2006 21:24:59.641 SQL Agent Service SQLSERVERAGENT was not previously running 06/06/2006 21:24:59.782 User authentication failed. Please check to see if the password provided is correct or check to see if the SQL Server Instance is set to Mixed Authentication 06/06/2006 21:25:07.860 Hotfix package closed
basically this is what I get and i am forced to cancel...
Microsoft SQL Server Management Studio 9.00.2047.00 Microsoft Analysis Services Client Tools 2005.090.2047.00 Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447) Microsoft MSXML 2.6 3.0 4.0 6.0 Microsoft Internet Explorer 6.0.3790.1830 Microsoft .NET Framework 2.0.50727.42 Operating System 5.2.3790
I configure Windows 2003 R2 and SQL 2005 two nodes Cluster. When I move cluster resource from one node to anther node it takes around 30 seconds to become online. So in that time if any query is running it stops responding.
We have a new failover cluster (Windows 2003 SP1, Microsoft SQL 2000 SP4) with each node of the cluster hosting 7 SQL Server instances in a 2-node active-active configuration connected to a SAN. We are planning to move some SQL Server Instances(from existing stand-alone servers) into this Cluster. Any insight into the process of moving SQL Servers into the cluster would be highly appreciated.
I am in the process of moving databases from a SQL 2005 Standard version to a 2-node 2014 cluster.All of my 2005 databases back up successfully.They all restore without issue except for one database that has a full text catalog. I get this message
Msg 7610, Level 16, State 1, Line 2 Access is denied to "fileStoragedataMSSQLSERVERFullTextCatalog", or the path is invalid. Msg 3156, Level 16, State 50, Line 2 File 'sysft_FTCatalog' cannot be restored to 'fileStoragedataMSSQLSERVERFullTextCatalog'. Use WITH MOVE to identify a valid location for the file. Msg 3119, Level 16, State 1, Line 2 Problems were identified while planning for the RESTORE statement. Previous messages provide details. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally.
[code]....
I went as far as giving the folder full access to everyone temporarily and received the same error.
Will I am running SQL 2005 Express on windows 2003 sp1 with a P4 3.4Gh (with hyperthreading), 2GB ECC RAM. I am running software that connects to SQL and reads/saves data. One of the programs which is CPU hungry manages data and the other manages the SQL connection and reads/saves the data.
My problem is that I am recieving an error that usually means connection to SQL is lost, this tends to be because the CPU hungry application is denying SQL from functioning.
However the annoying thing is the CPU use rarely passes 45% and in a another test environment never passed 5%. RAM is the same, consumption is less than 35%.
I have 1 problem here. I developed a desktop application and I am using SQL 2005 Express as my application database.
I found that after I closed my application, the SQL 2005 Express memory resources do not release. How do I release the memory resources except of restart the SQL services?
Do you guy have any better recommendation of other database is more suitable for desktop application?
We have an interesting problem. We are attempting to migrate from sql 2000 to sql 2005. the schema we have is exactly the same. the new 2005 box is more powerful than our 2000 box.
here is our schema:
tbl_Items ItemID int pk ReferenceID int sessionid varchar(255) StatusID int
tbl_ItemsStatus statusid int pk isinternalstatus bit
there is an index on (ReferenceID, SessionID, StatusID) and (SessionID, StatusID)
this is the query:
DECLARE @referenceid INTEGER SET @referenceid = 1019
SELECT MAX(i2.itemid) FROM tbl_Items i2 (NOLOCK) JOIN tbl_ItemsStatus s (NOLOCK) ON i2.StatusID = s.StatusID WHERE s.IsInternalStatus = 0 AND i2.referenceid = @referenceid AND i2.sessionid IN ( SELECT i3.sessionid FROM tbl_Items i3 (NOLOCK) WHERE i3.referenceid = @referenceid AND i3.status <> 7 AND i3.status <> 8 AND i3.status <> 10 AND i3.itemid IN ( SELECT max(i4.itemid) FROM tbl_Items i4 (NOLOCK) WHERE i4.referenceid = @referenceid GROUP BY i4.sessionid ) AND i3.itemid NOT IN ( SELECT MAX(i7.itemid ) FROM tbl_Items i7 (NOLOCK) WHERE i7.referenceid = @referenceid AND i7.SessionID IN ( SELECT i5.SessionID FROM tbl_Items i5 (NOLOCK) WHERE i5.status <> 11 AND i5.referenceid = @referenceid AND i5.itemid IN ( SELECT MAX(i6.itemid) FROM tbl_Items i6 (NOLOCK) WHERE i6.referenceid = @referenceid AND i6.status IN (7,11,8) GROUP BY i6.sessionid ) ) GROUP BY i7.SessionID ) )
GROUP BY i2.sessionid
we know this query is pretty bad and can be optimized. however, if we run this query as is on 2005 it takes about 2 hours to run...if we run the exact same query on 2000 it takes 9 seconds.
so this query on 2005 if run takes 2 hours..however, if we omit the s.IsInternalStatus = 0 or the i2.referenceid = @referenceid line it takes about 9 seconds.
why would this be? it makes no sense why omitting one of those where clauses would increase the performance of the query by 2 hours? we know its a bad query...but this doesnt make sense.
I have traditionally done web app and client server programming and am have been playing around with a new tool (Win forms) app that I eventually want to distribute to other developers and a couple of business people in our organization. I have done apps in the past that all connect to a central server for data access. The I'm working on now will have an individual DB per user and should be available locally for a desktop version of the software.
I am looking for more resources on things to consider when deploying an app with either MSDE or SQL 2005 Express. More specifically, items like long term maintenance of the db once it's installed with the user, etc. (DB bloat, transaction files, auto maintenance routines I may want to build in, etc)
I have seen all of the msdn docs on what you need to deploy (and how to do it), but I'm looking fro input from people that have deployed it and any significant pitfalls they have run into with that sort of deployment.
Any links or book references would be appreciated, thank you,
We are having some problems with the SQL Server 2005 Keep Alive mechanism causing dropped connections. The Keep Alive time and interval appear to work as documented. However the number of retries made before dropping the connection seems to be variable. When running Network Monitor I have seen this vary between 3 and 9 retries. The value for TcpMaxDataRetransmissions in the registry is set to 5. Does anyone know what the correct number of retries should be for SQL Server 2005 Keep Alive? Is this dynamically determined or is this a bug?
We moved a very tiny db (< 100 mb) from 2000 to 2005 (via attach/ detach) recently and now there are a couple sprocs which take forever to run from the app. Yes, the stats have since been updated as I reorg the indexes weekly. Now here is where it gets really strange, I can run this sproc in 0 seconds via Query Analyzer (QA). In other words I:
Have a developer run it from the app while monitoring in Profiler. There is a setting in the app for a 30 timeout, so after 30 seconds the app blows up and I see the exact code that the developer tried to run.
I can grab that code from Profiler, plop it into QA, and run it. Results are returned instantly. We have done this test a number of times, so I know it's not just a one time thing. How can this be? The only thing that got changed in the app was the connection string to the new 2005 server.
We are in the process of upgrading from SQL Server 2000 to 2005. During testing we came across the following situation.
To reproduce the issue you can do the following
Create this structure in a 2000 and 2005 server instances
CREATE TABLE test (a int, b varchar(30))
INSERT INTO test (a, b) VALUES (1, '2')
INSERT INTO test (a, b) VALUES (3, '3a')
INSERT INTO test (a, b) VALUES (4, '4')
Then run the following statement in both servers:
UPDATE test SET a = ltrim(rtrim(b)) WHERE b NOT LIKE '%a%' AND ltrim(rtrim(b)) <> a
In 2000 this last statement will execute with no problem and it will update one row, whereas in 2005 the following error message is given:
Msg 245, Level 16, State 1, Line 20 Conversion failed when converting the varchar value '3a' to data type int.
By looking at the execution plan it seems that 2005 first tries to evaluate ltrim(rtrim(b)) <> a and then excludes those rows containing a whereas 2000 first excludes those rows containing a and then evaluates the different than condition.
I know fixing this instance itself is easy but I€™m more concerned about having to rewrite many more stored procedures where we find this same scenario; is there any setting that can be changed to avoid this?
A stored procedure takes an IN parameter, an INOUT parameter, and returns an OUT parameter. When this stored procedure is defined in SQL Server 2000, the JDBC DatabaseMetadata method getProcedureColumns() returns three rows in the resultset:
one for the IN parameter (COLUMN_TYPE=1) one for the INOUT parameter (COLUMN_TYPE=2), and one for OUT parameter (COLUMN_TYPE=5).However, when the same stored procedure is defined in SQL Server 2005 (SP2), the getProcedureColumns() method returns only two rows in the resultset:
one for the IN parameter (COLUMN_TYPE=1) one for the INOUT parameter (COLUMN_TYPE=2).No row for the OUT parameter is returned.
jTDS JDBC driver was used for both of the above tests. When the Microsoft JDBC Driver for 2005 was used against SQL Server 2005, the same behavior (only two rows in the resultset) was observed.
Has someone else run into such a problem? Is this a bug in SQL Server 2005 because the same jTDS driver works as expected against SQL Server 2000 but not against SQL Server 2005? Any feedback will be appreciated.
Note the code below, running on the version noted.
I just found this today, figured I'd share. Not sure if it's a known bug or a "special" feature. The only difference between the two queries is the 3rd line, everything else is the same. Notice that the sort order changes, yet no errors or warnings are given. I assume that the table aliases are ignored for the order by, unless there are duplicate column names in the results.
I abstracted this from a bug I discovered in one of my apps today, where I have sales reps assigned to their clients with start and end dates. A while back, a developer asked me to format the dates without the time portion, and when I did so, I introduced the problem. I resolved it temporarily by ordering by convert(datetime, startdate) but I found it strange that the column alias match overrides the table alias attempted match in the order by. Another way to get around this would be to change the column aliases, then the sort order would be as desired, but I didn't want to have to change the app code for something so trivial.
My apologies if this is a duplicate.
@@version = Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
select convert(varchar(10), table_alias.startdate, 101) as startdate,
convert(varchar(10), table_alias.enddate, 101) as enddate
from
(
select convert(datetime, dateadd(mm, -4, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -4, getdate())) as enddate
union
select convert(datetime, dateadd(mm, -3, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -3, getdate())) as enddate
union
select convert(datetime, dateadd(mm, -2, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -2, getdate())) as enddate
union
select convert(datetime, dateadd(mm, -1, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -1, getdate())) as enddate
union
select convert(datetime, getdate()-1) as startdate, convert(datetime, getdate()) as enddate
) as table_alias
order by table_alias.startdate
select convert(varchar(10), table_alias.startdate, 101) as startdate,
convert(varchar(10), table_alias.enddate, 101) as enddate,
table_alias.startdate, table_alias.enddate
from
(
select convert(datetime, dateadd(mm, -4, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -4, getdate())) as enddate
union
select convert(datetime, dateadd(mm, -3, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -3, getdate())) as enddate
union
select convert(datetime, dateadd(mm, -2, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -2, getdate())) as enddate
union
select convert(datetime, dateadd(mm, -1, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -1, getdate())) as enddate
union
select convert(datetime, getdate()-1) as startdate, convert(datetime, getdate()) as enddate
We are planning to upgrade the SQL Server in our production environment from SQL Server 2000 to SQL Server 2005. This is a 4 Node cluster environment with 3 Databases on 3 Virtual instances. The main requirement is to achieve this with no/minimal downtime.
Could you please suggest or direct me to any documentation for the best practices used to upgrade such an environment?
Here's my situation and I've spent enough time to troubleshoot. Any help would be appreciated.
1) I have a VB.NET Windows Form Application that uses SQL Server Express 2005 database. 2) The "mdf" file is a part of the application itself. 3) When the application distributed to my clients using Windows XP SP2, the "Click Once" installer automatically installs SQL Server 2005 Express [which is a pre-requisite] and copies this mdf file into the application folder. 4) The authentication mode is "Windows Integrated"
The problem is, when the application is launched [while logged in as the local system administrator in a given computer], it is unable to connect to the SQL Server. I get the following error,
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance
Here's my connection string
Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Ragas.mdf;Integrated Security=True;User Instance=True
I have this website which has sql2000 database. this website is using the membership class of asp.net 2. when you run it on the localhost, no problem. after I upload it to my webhost, the usernames that I created with the membership class wont work anymore. membership class raises error "invalid username or password". i checked the database and the username are still there. that left me no option but to create another username. why is this happening? is there something wrong with the way I uploaded the mdf database file?
We're upgrading a SQL Server 2000 cluster (Active/Passive) running on Windows 2000 Server to a SQL Server 2005 Cluster running on Windows Server 2003. We can't purchase new hardware and we have no spare hardware. We also need to move from Windows 2000 Server to Windows 2003 Server at the same time. We want to keep downtime to a bare minimum.
What we were thinking was the following steps... Anyone try this?
1. Break the link between the servers.
2. Install a fresh copy of windows 2003 server on one side along with SQL Server 2005. While this step is running, the active node would still be live on Windows 2000 Server and SQL Server 2000 serving our customers.
3. Restore a copy of a backup from the active production side to the node we're upgrading and at that point we would bring the active node down, switching the active node to be the newly upgraded server.
4. As a final step, the old active node would now have the link to it broken, we would install a fresh copy of windows 2003 server on it and sql server 2005. At this point we would bring it back into the cluster and the cluster would be complete again.
Need your help and guidence for doing upgrading SQL Server 2000 Cluster to SQL Server 2005 Cluster.
Let me explain my current environment.
1. Currently SQL Server 2000 Cluster environment is running on Windows 2000 Server we need to upgrade this to SQL Server 2005 on Windows 2003 Server. >>> Production environment.
My Plans:
1. On Testing Environment Install SQL Server 2000 cluster on Windows 2003 Server and do a restore of databases from the produciton environment.
2. Upgrade In-Place from SQL Server 2000 Cluster to SQL Server 2005 Cluster.
My doubts
1. Can i install SQL Server 2000 Cluster on Windows 2003 Server. Is it possible or not.
I have this website which has sql2000 database. this website is using the membership class of asp.net 2. when you run it on the localhost, no problem. after I upload it to my webhost, the usernames that I created with the membership class wont work anymore. membership class raises error "invalid username or password". i checked the database and the username are still there. that left me no option but to create another username. why is this happening? is there something wrong with the way I uploaded the mdf database file?
We are writing an enterprise application in which the user logs in by entering their username and password in a form. The user will enter their actual Active Directory credentials. I want to pass the username and password to a stored procedure that will check for a valid user account in an Active Directory. In Sql Server 2005 I know I could write a small .NET library that uses System.DirectoryServices , register it in SQL and access it from an stored procedure.
Can it be done in Sql server 2000?
The reason we are not just autheticating in the .NET code of the application is that we found that it does not work on Windows 98 cleints, hence the need to go through sql server.
I've got a windows application that needs to authenticate to the active directory. But the only members of certain group can start the application. for example only the administrators of the active directory can start the application.
M trying to create a logon form I had something in mind but i can work it out yet
When the user wants to login into the application the value of textbox must be compared with the datafield in the sql server. And then i want a messagebox to show up.
I created a table in sql server with 2 fields in it , User and Password.
I€™m working on an application that needs to support multiple users without log in and out of Windows. I would like to use trusted/integrated security (domainusername) so I.T. does not have to manage two accounts per operator. Is it possible to use trusted accounts (domainusername) in the SQLClient.Connection object?
I have a NT Group of users that need to connect to MSSQL db using integrated auth and run a stored proc. I have got this concept to work for individual windows users but not a group.
Does anyone have any ideas?
(Win XP, MSSQL 2000 SP3, Windows Group = "SQLUsers", member is "User2")
--- TSQL ---
USE master GO sp_grantlogin 'pcSQLUsers' GO sp_defaultdb 'pcSQLUsers', 'theDB' GO USE theDB GO sp_grantdbaccess 'pcSQLUsers', 'SQLUsers' GO GRANT EXECUTE ON [dbo].[uspThing] TO SQLUsers GO
-- Trying to connect with OSQL.EXE:
> osql -d theDB -E Login failed for user 'pcUser2'.
BUT.....
runing it for the individual user:
---TSQL
USE master GO sp_grantlogin 'pcUser2' GO sp_defaultdb 'pcUser2', 'theDB' GO USE theDB GO sp_grantdbaccess 'pcUser2', 'SQLUsers' GO GRANT EXECUTE ON [dbo].[uspThing] TO SQLUsers GO
---- now trying to connect with OSQL.EXE as User2:
I tried using Query Notification on my computer at home: * Win XP Pro with all the SPs and hotfixes * SQL 2005 with SP1 qand hotfix
Query Notification worked fine.
Then I tried using it at work:
* Win XP Pro with all the SPs and hotfixes
* SQL 2005 with SP1 qand hotfix
and I see the following error in the SQL server log file and notification does not get to the client app:
---------------------------------------------------------- Date 9/1/2006 10:18:30 AM Log SQL Server (Current - 9/1/2006 10:18:00 AM)
Source spid17s
Message An exception occurred while enqueueing a message in the target queue. Error: 15404, State: 19. Could not obtain information about Windows NT group/user 'domainmyuser', error code 0x6e. ----------------------------------------------------------
A similar error shows up in the machine's Event Log.
I am sysadmin and full OS admin on both boxes. The difference is that the computer at home is standalone while the one at work is part of a domain.
Hi. I have a DetailsView with Bound Fields "Login" and "Password". This informations are stored in SQL database. How to solve such authorization? How to compare password stored in database against passowrd typed by user? Is this a good idea to use CustomValidator control to write some checking procedure?. Regards. Pawel.
