SQL Server 7 Switches To Windows Only Auth On Reboot
Jul 20, 2005
Hi All,
I have a SQL Server 7 installation running on a windows 2000 server.
SQL Server switches from mixed mode authentication to Windows Only on
reboot. Has anyone else experienced this? Any help would be greatly
appreciated.
Service Pack Reports:
Microsoft SQL Server 7.00 - 7.00.1077 (Intel X86) Sep 6 2002
15:10:15 Copyright (c) 1988-2002 Microsoft Corporation Standard
Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
I only see two strange entries in the log:
2004-05-01 00:38:09.55 spid37 Using 'xpsqlbot.dll' version
'1998.11.13' to execute extended stored procedure 'xp_qv'.
2004-05-01 00:38:01.71 spid1 Failed to obtain
TransactionDispenserInterface: Result Code = 0x8004d01b
I don't believe that either of these are related to the configuration
option for Windows Only, though.
I had a few packages created with Windows Authentication which now needs to be migrated to run with SQL Server Authentication. I tried changing it in the connection managers but the packages failed to validate at the SCD transformations.For a few packages I had Configuration files for these packages where i tried to do it but it dint run giving the following Error Message: [Connection manager "Target"] Error: An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user <<with my network id>>.".
[Execute SQL Task] Error: Failed to acquire connection "Target". Connection may not be configured correctly or you may not have the right permissions on this connection.
We are having problems in our project. We have ETL packages we call from our web app that we want to run and the user is the logged in one in the web.
We use Windows Authentication in our web app and we used the CreateProcessAsUser to impersonate the user. <identity impersonate="true"/> is also in our web.config.
We were able to call the dtexec successfully (with the user for the process as the logged in the web -- thus impersonation was successful.)
But, a problem arises when we use Windows Authentication for the connection of the ETL to the db (SQL Server). When we run the ETL on our local machine using dtexec/dtexecui, it works fine.
But when we ran it in our server, it fails. According to the event logs, it can't connect to the db server even though impersonation is successful.
Btw, the client, web server, db server are three different machines.
Hi-I have a program that I am developing on a laptop, then deploying it on a server.I have the prgram running passing a username and PW in the connection object, but like the idea of using windows authentication MUCH better.I just joined the domain, so I am domain/me for example.If I log into the server, and look at securities, logins and added domain/me to the logins.I then try and set up a sql connection via both visual studio, and sql server magt studio, and get the dreaded "Cannot generate SSPI context" error. Anyone else have this problem? SHOULD V.S. be using domain/me to connect? TIA dan
I am searching for a Powershell script which picks Windows Server names from SQL server table(eg: Instance.DB.tbServerList) & writes last reboot date to SQL server table(can be same or different table).
When creating connections for source and destination in a package what is the recommended approach, window authenticatation or sql? I will be deploying to filesystem, what would be best.
this is my first ssis package, so pardon my ignorance.
Howdy,I have a SQL server in a completely different forrest than theworkstation I am running query analyzer on. However in query analyzerwhen I choose windows authentication I cannot manually specify"domainusername" as I could when mapping a file share across domainsor forests. Is there any way around this without resorting to using SQLServer authentication?Thanks
I cannot log in to SQL Server 2005 Dev Edition in my local machine using Windows Authentication. The server returned "Login failed..." when connecting with SQL Server Management Studio.
I have not change anything since installation of this server.
This problem happens in RTM and SP1 versions, both running on Windows Vista RTM.
Anyone having this kind of problem too? Any solution? I'm guessing it's Vista-related.
Is there any way to provide 'Windows' credentials to connect the Management Studio to a remote SQL 2000 DB when I am not logged on as that user (on the dev machine where I am running Mgmt Studio)?
I do of course have local machine admin rights via an AD domain account added to the machine administrators on the remote SQL box itself.
Hi Guys, I have an Intranet web application and I have to use windows authentication. They are 4 main users group to access the application, each with different set of permission on my store procedures. I have created a windows group for each of those group in my windows environemnt. My DB Connection String in web.config is set to Integrated Windows Authentication ( Integrated Security=SSPI) I have done all configuration based on this article: http://msdn2.microsoft.com/en-us/library/ms998292.aspx I have 4 different database Roles in my database and 4 different database users. I also add NT AUTHORITYNETWORK SERVICE as a member of each of those database Roles. Now I get connected, fine and set permission on the database roles. 1 thing I want to know, How Can I get the Windows Group name in the database, which has the same name as my SQL Login and Database Roles or User I tried all SUSER_SNAME() and ... they all return NT AUTHORITYNETWORK SERVICE I want to get AppPowerUser Role or windows group name. I think I should be able to because I am using windows authentication. Any help is appreciated. Mehdi
Does anyone know of command line switches/switch that will enable the use of "multiple processors" on an HP LHS 400 Mhz [4] processor box? I believe there is a way to "tell" the server to utilize all processors while in SQL 6.5....
I am stuck... I am trying to use a linked server in sql 2005 (xls sheet), it works great using windows authentication but since the program will not be running locally i would prefer to use sql auth...
when i log in using the sql name i have created. it only has connect & execute permissions on the main database (as it should), but i also need to query this linked server, when i try to query it i get authentication failed. how do i fix that?
the xls is on a fat32 partition and can be moved to an ntfs partition with out any problems.
I'm very new to SQL Server. I need to reboot the server for some reason. I need your help to list out what a the step that I need to follow to perform this process.
Hello,I have a server running Windows 2000 Advanced and Sql Server. Thesystems runs fine throughout the day but reboots at night, somtimesmultiple times. The event log has a message that reads "The previoussystem shutdown at X:XX PM was unexpected."Any ideas?Paul
We have to add some hardware and I was wondering if someone could provide a stepwise tip to do it .
It is a 3 Server Environment Pub/Dist/Sub All three running under SQL2K Here is what I am planning.
a. Uncheck the Enable in Distributed Agent for each replication b. Wait for all replications to complete c. Shut down Publisher ? Or do I have to do something else before that.
An IT dept. I have been consulting with has started to reboot SQL server every night. They are saying that this is the best practices. I would like to know if anyone has any other ideas on this subject. What is the best practices for how often a SQL server should be rebooted, daily, weekly, monthly ... ?
I seem to be having a strange problem with accessing the SQL Express database under the following configuration.
Install Longhorn Server Beta 2 Add WSS role, setup default site, everything seems to be working fine (using NTLM auth for the site). Content database clearly working fine to the embedded SQL Server Express. Want to switch to Forms Based Authentication with SQL Membership Provider. Follow insructions for it and discover the following issues accessing the database:
aspnet_regsql (running locally on same box) fails and says it can't connect to the database try using the surface area configuration tools to enable remote access/ protocols etc. aspnet_regsql still fails and says it can't connect to the database Install the SQL Management Studio Express - try connecting it to the databse to explore the tables etc. SQL Management Studio Express fails to connect to the database Try switching client config to connect via shared memory rather than over TCP/IP etc. Still fails to connect to database Try install a third party tool called MSSQL Maestro (does some similar things to SQL Management Express) Lo and behold - MSSQL Maestro connects to the database just fine, can explore the tables etc. No problems. Try aspnet_regsql again, try SQL management express again after doing lots of things (try restarts etc. restarts of services). No luck.
So I'm confused. WSS seems to connect to the SQL Express config database fine. MSSQL Maestro connects to the database fine and will allow me to explore it. Using the exact same host and database name (the WSS configured default) aspnet_regsql and SQL Management Studio express both fail with the exact same error.
EDIT: Some significant additional details:
- I have two databases .MICROSOFT#SSEE (which is the WSS default internal database) and .SQLEXPRESS (installed when I installed the rest of SQL Express to get the management tools etc.)
- I can connect to them both by command line with sqlcmd -S.MICROSOFT#SSEE and sqlcmd -S.SQLEXPRESS just fine. They're both running.
- I CAN connect to.SQLEXPRESS from SQL Management Express Studio, but I cannot connect to .MICROSOFT#SSEE
- A look at the error log, shows a line like the following about listening on the named pipe for SQLEXPRESS database, but I don't see a similar entry for the MICROSOFT#SSEE (even thought it's configured to do so in configuration manager...)
"Server local connection provider is ready to accept connection on [ \.pipeSQLLocalSQLEXPRESS ]."
- I've run SQL Management Express as administrator - no change
- The error I get is either error 26, or error 40 when using Shared Memory protocol
- I get the same error and error code from aspnet_regsql
I have transactional replication set up between two dedicated servers. Server A is the PDC and Server B is a BDC (they are both Win2000 boxes). Both the servers are brand new, and replaced the two that were running like clock work (replication wise) for the last 12 months. I never had this problem with the old servers....
When the servers are shut down (as the case was a couple of weeks back with a power failure) or just recently when they were move to another room. Both servers boot up at the same time. Server B (which is the server holding the db being replicated) boots quicker and as a result replication fails and is then 'sucessfully stopped'. Unless I am aware of the server being rebooted and can monitor this potential problem, within 2 days the logfile grows to large and everything comes to a crashing halt.
I just remove replication, truncate and shrink the log, reset replication and we're away.... BUT I really need to know why it is happening in the first place. I figure there must be a setting that I have forgotten about or something.
To All,I have a SQL2KSP3a database(<1GB) running on a 4x3GB physical CPU with4GB of ram. It is Windows Server 2003 with hyper-threading turn on.There are ~420 .Net users/cxns (fat client, no web/app servers) withconnection pooling and ~1 trx/sec. The database growth is neglegeableand actually is not even relevent which I will explain in a minute.99% of the trxs are from one SP that does a select. The resultsets arerelatively small as well 1~100 rows. Yes I have tuned it with indextuning wizard as well, changed the SQL memory configurations, etc....My problem is this...The first day after a reboot, the server runs 6%CPU during peak hours.During the non-peak hours until the next day something apparentlyhappens. The next day (2nd day after a reboot), it jumps to 40%CPUduring peak hours. The server will continue to run at 40%CPU duringpeak hours until the next reboot. This phenomenon has been occurringfor 6 months or more and the traffic on the server is the same for day1 as it is for day 2,3,4,... This database was on another server with100+ dbs and exibited the same behavior, thus bringing that server toits knees, and thus we had to move it to the server in question with noother dbs.I have googled my eyes out, Microsoft site, white papers, perfmon,SQLDiag, PSSDiag, execution plans, index tuning wizard, and the listgoes on! I currently have a case open with Microsoft that has beenopen for months now. I have been passed around to the 3rd "MS TechSpecialist". I have ran PSSDiag a total of 6 times for them for hourson end. I have changed MAXDOP. I could give more information, but Iwould be here for days. I am running out of patience/ideas andMicrosoft is apparently blowing smoke.Any ideas are greatly appreciated!Thanks in advance!JL
If I am running SQL Server 2005 in a clustered environment, what is the safest way to restart the SQL server agent? It is currently running but I need to restart it for maintenance purposes.
Is the safeway way to restart is to login to the SQL Server Management Studio as the system administrator, select the SQL Server Agent object, right-mouse click and select "Restart?"
How many times per year should we schedule time to reboot a SQL Server 2005 Cluster? We want to do this to defrag the memory. What I am looking for is a document from Microsoft or a consulting firm that has a good reputation that I can show my manager.
Sorry i think i may have posted this in the incorrect forum before - if i have done it again here can someone tell me where i should post this please, thanks:
Hi, we are having problems with a server Intel RZeon 3ghz, 3gb ram running 2003 service pack 2 with a 70gb drive and and 400 gb drive all with adequate free space. There are 6 hard disks in total and i assume operating at least RAID 5. We have SQL2000 server with a few standard sized databases and a connection to one other server.
A few months ago the back up of SQL server databases started taking 4- 5 hours when before it took 20 minutes. We had actually lost one of our disks in the RAID array and it before this was spotted by our engineers we reindexed the sql databases and defragged both 70gbC: and D: 400gb drives hoping to correct this slow down. Unfortunately the new disk had not been correctly seated and this was why it was taking 4-5 hours. After fixing the disk the backups took 12 minutes again but then started taking 2-3 hours after a few days.
The reindex/defrag did seem to improve the speed of the backups to 12 minutes (from 20 minutes) when the backup did function correctly (also the sql databases' performance improved). However the backups only take 12 minutes after a server reboot - this can last from only 2, up to 5 backups(days) in a row before a slow down to 2-3 hours and again only a reboot will sort out this problem.
NB this intermittent slowdown only occurred after the disk failure.
We have tried monitoring SQL server and can find no CPU/RAM intensive clashes or long running jobs interferring with the back up. Does anyone know what might be going on here? and if there are any server monitoring tools that may help us discover what is causing this problem ?
I have a situation that I was wondering if anyone has ever ran into before. It has to do with one of my MS SQL servers. The hardware is a ALR/Gateway 9000R with 4 PP200's, 1 Gb RAM, and a RAID 5 with 72 Gb storage. The NIC card is an ATM 155 Mbit card connected directly to our fiber backbone.
I have WinNT 4.0 Server Enterprise Edition loaded with SP4 and MS SQL Server 6.5 Enterprise Edition with SP5 installed.
I have 7 seperate active databases on the server supporting 7 different applications. The server has been on-line for approximately 4 weeks and just recently (last Thursday) it has started to "lock up" every couple of days. By lockup I mean that it starts to reject all requests by all users. No one can connect to the server including myself. The MS SQL error log grows and grows until we reboot the server. The error logs are 100 Mb or larger in size due to rejection errors being repeated over and over again.
There has been no change made to the server since initial installation.
The error in the MS SQL error log that keeps on being repeated is... "Message 17308: Kernelerror - Lazywriter. Process (process ID number) generated access violation; SQL Server is terminating this process."
We have an incident in with Microsoft but they are not responding fast enough.
I was hoping that someone out there may have had this type of occurrance happen before.
This computer gets set to autostart the SQL Server Service, which is set to local, (have also tried local network and user specific), and everything works fine. But when it gets rebooted, the service refuses to start.
We disabled the Mcafee and Windows firewall. No effect.
The SQL Browser and Full Text Index Services start automatically just fine.
Hello, SQL Server 2000 services getting stoped automatically when system get reboot. When system reboot they started and after 5 minutes they were getting stoped. I went through the event log file and found that an error is occuring in sql server with number 17148.
And I also observed that there is a file called "C : P r o g r a m F i l e s V E R I T A S B a c k u p E x e c N T b e n g i n e . e x e" is executing during the maintenance process,which is stopping the sql server services(agent and olap)
This problem is generating error in sql server with above mentioned number.
I do not understand why an SSAS cube disappears after a reboot. We schedule a reboot for our server weekly and after the reboot. I have to deploy my SQL server Analysis Database. It is just gone after a reboot and why this happened. I searched a lot on internet but it seams that I am the only one on this world with this issue.
How I can configure the server in such a way that the SSAS cubes will not disappear after a reboot.
Hi I've got a sql server 2000 database that when running is runnign fine. About 9 months ago I altered one of the stored procedures and ever since then when the machine is rebooted the stored procedure is "reverted" back to the old sproc... ??? is there any way I can recrete a sproc in a job that runs every day?? why would it be doing this?