We are running MSSQL 2000 on a reasonably powerful dedicated Win2k system Dual 2.4 Xeon Procs, 2 GB of RAM, Ultra 160 SCSI disks.. blah blah blah.
Our primary use this database server is for our Coldfusion Application servers. We host about 50 or so databases.
Of late, we have been experiencing random failed logins for certain databases. These failed logins are logged to the event log on the server. They randomly; queries executed against a datasource configured on our application servers trying to get data from the database fail with a login failure when other times they will succeed. I am confused about this issue. Could an issue where the SQL Server is overloaded cause a login failure for a database?
The event log is showing a ton of failed sa logins. The server is connectedto the net. I am assuming this is a dictionary attack to get the sapassword. I am trying to find out if this is an inside attempt or from theoutside. While the profiler will tell me which program or script is sendingit, how do I find out which ip address(s) from the net is doing this?--John Dalberg
SQL 7 profiler has an event in the Misc. category of Failed Login. It does not, or at least I cannot get it to, produce any output when a failed login occurs. Any hints?
I tried this because every week or so I get this in the error log: Login failed for user 'Admin'. It occurs several hundred times within a minute or so. It obviously has to be an automated process as you couldn't click a button or press a key 13 times a second.
The login does not exist as a SQL login so I can't tell which database it is trying to get at. Any suggestions gratefully received.
We're using a canned app that only uses SQL Server logins (it will NOT use Windows logins to access SQL Server)
SQL Server logins seem to lack even the most rudimentary security features such as expiring passwords and automatic disabling after a set number of failed logins. Bad. Bad Microsoft.
Has anyone figured out a way to graft this on after-the-fact?
I can do it in an awkward fashion by auditing failed logins and going back to read the error log, but this isn't real time by any stretch.
I need to be able to get info about failed login attempts which need to be running all the time and logging info such as hostname , application name, host machine, etc. The best way I can see to do this is by crerating a trace. Then loading the info from the trace into a table for later use by using fn_trace_gettable. But this requires that the trace be stopped and the definition removed for the trace file to be populated.
Is there a way to be able to log this info constantly into a table without having to stop and then recreate another trace. Also it requires that the table created by the function is dropped.
I just restored a database on a new server with a backup(complete backup stored in backup device) from another database on another server using the "with move" option. In fact here was my process:
Alter database ngauge SET SINGLE_USER WITH ROLLBACK IMMEDIATE
restore database ngauge from disk = 'C:Program FilesMicrosoft SQL ServerMSSQLBACKUPgauge1.BAK' with move 'NGAUGE' to 'C:Program FilesMicrosoft SQL ServerMSSQLDatagauge_Data.MDF', move 'NGAUGE_Log' to 'C:Program FilesMicrosoft SQL ServerMSSQLDatagauge_Log.LDF'
Alter database ngauge SET READ_WRITE
it worked.
But it did not move the 98 or so users/logins...from the source database what is going on??? what am I doing wrong??!!
I just applied instcat.sql to my SQL Srvr 2k sp4. At first it broke my replication with some cursor errors. Applying the latest instcat.sql fixed the problem, however one of the distribution agents would not start (i'm not sure if instcat had anything to do with it) giving me a "Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. The process could not connect to distributor 'DistributorName'. The step failed." error.
I was checking the logs because of some failing backups and found literally hundreds of enties for failed logins for user sa. This is does not seem to be related to the failed backups, because even after fixing the problem (disk space), the entries continue to pile up.
Needless to say, the information provided in the log is a bit sparse: date and time, source (Login), message (Login failed for user 'sa'.). If that is actually enough to go on, then there must be some way of cross-referencing this information with that found somewhere else. Is there a 'somewhere else' or are there other things I need to do in order to track this down?
I don't suspect any malicious intent, but I'm not ruling it out. My main concern at this point is twofold: to gain a better understanding of system management; to identify and correct the failed login.
Any recommendations, whether tools, documentation, or otherwise are very much appreciated.
ok, first, I know... I forgot to run a backup of the master database, and I forgot to run a script to caputure logins. Not that that is out of the way... I need to recreate the logins under the Securities tab below the databases. All the company databases have the user names and passwords assigned to them, but they are not able to login, because they are not able to authenticate to the SQL server first.
Is there a script that someone has that will copy the company database security info for the users and recreate them in the SQL security tab?
I know that I can rebuild them manually, but I need to delete them first in the application software, then delete them from the databases, and then recreate them in the application software... and as simple as that sounds... it is a slow moving process.
Hi,I need to extract randomly 5 records from the table "Questions". Now I useSELECT TOP 5 FROM Questions ORDERBY NEWID()And it works. The problem is that I need an additional thing: if SQLextracts record with ID=4, then it should not extract record with ID=9,because they are similar. I mean, I'd like something to tell SQL that if itextracts some questions, then it SHOULD NOT extract other ones.How can I do it?Thanks!Luke
Hi Iam developing online test using ASP.NET,C#.NET.Now my doubt is test takers should get random questions from the database sqlserver2005.Already i ve inserted 10 question into the database.I ve used NEW ID() .But questions are repeating.I am new commer in to IT industry .Provide me with code.Given below is my code.I can retrive questions from the database now but cannot get in random order.protected void Page_Load(object sender, EventArgs e) {Label10.Visible = false; Label9.Visible = false;if (!IsPostBack) {if (Session["id"] == null) {Session["id"] = 1; }Session["ans"] = 0;SqlConnection con = new SqlConnection(@"server=123-9181FF31362SQLEXPRESS;user id=sampleu;pwd=Sampleu2;database=n;"); con.Open(); SqlCommand cmd = new SqlCommand("select * from n3 where sno='" + Convert.ToInt16(Session["id"].ToString()) + "' ", con);SqlDataReader re; re = cmd.ExecuteReader();while (re.Read()) {
SqlConnection con = new SqlConnection(@"server=123-9181FF31362SQLEXPRESS;user id=sampleu;pwd=Sampleu2;database=n;"); con.Open();SqlCommand cmd = new SqlCommand("select * from n3 where sno='" + Convert.ToInt16(Session["id"].ToString()) + "' ", con);SqlDataReader re; re = cmd.ExecuteReader();
Hi, I need to select few items from sql database.I know for ORDER BY, but I need those items to be mixed in random order every time when they are returned from database. Those are the same items every time, just randomly mixed. Can i do it with SQL, or I have to find another way (e.g. to mix them after sql returns them)?
I'm using ASP and SQL Serv 2000. What I need to get from 2 tables (company & customers) is random 10 customers from random 20 comp. Anyone got an idea how to do this??? I've spent 2 days trying to get stored proc. or T-SQL to work, but nothing good came out of it. I can get 1 comp and 10 cust, but not a grouped list of 20 comp. w/ 10 cust. each.
hello i have a problem and i dont know if it can fixed or not i'm using asp.net 2 and sqlserver 2005 i have a table named questions in the database and that table has 5 columns questionid,question,answer1,answer2,answer3 every questions has 3 answers 1 right and the other 2 are wrong what i want to do is select 10 random questions and their right and wrong answers show in my asp.net page as application the answers will be in radio buttons so is that can be done or it's not possible.thanks all
I am administering several SQL Servers running SQL Server 2005 SP2 Build 3042. I have a common maintenance plan that runs on each of the servers. The maintenance plan runs fine on all the servers except for one. On the one server the Database Integrity check fails with the following error:
Check Database integrity on Local server connection Databases: <list of databases> Include indexes Task start: 2008-02-21T00:05:42. Task end: 2008-02-21T00:05:46. Failed0) Alter failed for Server €˜XYZ€™
I created a test maintenance plan to just do the integrity check and selected one database only and this also failed with the same error message. I ran this test maintenance plan and configured it for each of the databases in question and it failed each time. If I run the DBCC manually against the databases they all report fine.
I read some of the post that talked about the €œAllow Updates€? being set incorrectly but that does not apply to my problem since my configured and run values are set to 0.
I would move a Database to another server. I try to use DTS but I have problems with this process because DB have big tables, I think. I try to use DETACH and ATTACH procedures but logins doesn't export. And more, in new server there are already logins from another DBs.
What's the best way to solve this problem? Please, help Thanks
I am a systems analyst and work with an app that runs against 2 SQL Server DBs. Though I have some familiarity with SQL Server and SQL, I am not a DBA.
The app executable is tied to a Windows service. When we install the app, we run a process that builds 2 dbs to include: Tables, indexes, stored procedures, views and user accounts. SQL Server is set up for mixed mode authentication.
Normally, the dbs run off the local db user accounts which are tied to local logins with the same names. We have a client that wants to remove our standard logins so that they can run on only a Windows login. I know I should be able to tie the db users to a Windows login. And I can do the same for the service.
But I am at a loss as to how to get this done. How do you associate db users with a Windows login? When I have tried sp_change_users_login I get an error that the Windows login does not exist. (Though I have added the Windows account to the DB.)
Hello EveryoneI am in the process of creating a web application that will allow me to have one place to go to see all of my SQL Databases. I would like to have one of the pages that showed the current status, or maybe next to the name of the server, have the status listed.I know how to create a database connection string using the web.config But I will need to have a query that loops thru a list of database server names, and each of them returning the status. Being able to connect, query the status of the server, show the status of each database. My database connection string will only log into one database. How can I code this to be able to loop thru the list of stored database server names, and then be able to log into each database server?Can I use a separate class with a method, and pass in the name of the server? That means that I would have to have the same user name on all the database servers, wouldn't I ?I am looking for a way to accomplish this. I am open to suggestions. I am coding my web app using C#. ThanksAndrew SQLDBA
I can't figure out what the purpose of having seperate users is as I can't actually login to the database using one.
Here is my scenario.
I have a single login called LoginA and I have a database which I want to carve up using schema's. At the database level I need to create a user, associate a login with this user and can set a default schema and specifiy what schemas this USER can access. The login created can access multiple schemas.
So..
I created a database login called loginA.
I created a user for the database called UserA set it's login name to LoginA and
I then created 3 schemas called SchemaA, SchemaB and SchemaC and set their schema owner name to UserA.
I went back to UserA and set their default Schema to SchemaA
How can I login using the new user created as it has no password associated with it. If I login using LoginA then I have no default Schema set becuase the schema is associated with a USER not a LOGIN.
I can understand why you can only have one login account assicated with one user account for each database but I can;t understand why you can specify a user name if you can't use it to login.
When I a set of databases from backup all login information is lost. The front end of the application provides a way to create new users, but it is a very long process. I am looking for a script or stored procedure that will help attach the users to the database.
Does anyone have a script already which pulls all the logins and what databases they have public/dbo access to? I'm looking for something more than just pulling syslogins/users and their default dbname, I need a list of all the databases they have dbo access to.
Hi I took a Full Backup on Production Server and Restored the Same Database on Testing Server, i am able to see all the Tables Stored Procedure and View except Logins, i am not able to see any logins, there are total 650 Logins under the Database how can i see the logins and how to restore the logins, while i am trying to create any logins it is saying Error Occured login is already existing in the Database..
We have a particular database sat on SQL Server 2012 box along with about 20 other databases.
What I require is a method/Script/Audit that will simply track anyone who logs (successfully / unsuccessful) into this one particular database on the server (The single database is the key as the end user does not want information on any of the other databases that sit on the server), it also has to log time the attempt was made and it must track the logins via SQL Server or the application itself that is attached to the database.
I use detach/attach method to move database to a new SQL Server 7 on MSCS. The security information was lost in the new server. Database Access permission was gone and I failed to select it again because message showed the database role is already exist. The default databases were also posted incorrectly. Would you please to tell me how to fix this type of peoblem or let me know how to move the login info such as login id, password, database permisson to the new server which locates in a different machine.
Query to show logins that don't have any permissions within the SQL instance? I'm tasked with doing some cleanup and have found some cases where the database was deleted or moved to another server but the logins that used it were not deleted. I'd like to identify them to research.
For instance a query to show logins that have no permissions in any of the existing databases would be handy. I'm thinking it would be complicated by the need to loop through all of the existing databases and then outer join it to the list of instance level logins. Going to try to write something like that but was hoping that a script already exists.
I am working on a Login migration issue. I have the Master database from the original database, that I have attached as a User Database, call it OriginalMaster. I also renamed the mdf and ldf files, before attaching, just in case.
I want to select the logins from the OriginalMaster, and using sp_help_revlogin script all the Logins out. I know there were many logins in that database. But what I am seeing, are all the Logins from the Current Master database. Which are only 7 Logins.
How can I select the logins, or script out the Logins from the OriginalMaster database?
Our company has 2 Database Roles (DBE and DBA). The DBE creates database schema, performs SQL Server Administration, and manages server security. The DBA writes data access, ETL, and manages database security. In 2005, we're struggling with how to allow the DBA to see all of the logins on the server in order to add them as users of their database. What permissions does the DBA need to select from any of the logins on the server to add them to their database?
Does anyone know a way (a query perhaps?) to determine which logins have write permissions to a specific database on SQL Server 2005? Ive thought about joining sys.syslogins & sys.sysusers but looking at the columns, not sure which one would render me that info.
I am in the process of implementing database mirroring. I am trying to get an understanding about manageable ways of keeping sql user logins synced between instances.
I have looked at KB918992 which makes sense for the initial intallation, but what about keeping passwords synced in the long-term. I don't want to have to manually set a users password on the failover every time they change it.
How is everyone else accomplishing this using sql logins (not windows auth)? Pointers to articles and/or documentation would be great.
I also migrated the server level logins using SSIS transfer logins task, available on SSIS 'Transfer logins', I selected all the databases that I have migrated so that I have all database users account in server logins (to avoid orphaned users); but I don't have all the database users in server logins, also the sys.sysusers doesn't have the database users, that I have moved to SQL 2005. Can anybody help?
Also do I need to back up the SQL Server 2000 database and restore it on SQL 2005? What impact this operation can have?
I have read the previous threads on the bugs with this task mainly: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1438968&SiteID=1 . These are great posts that helpmed me avoid wasting time. I haven't seen one yet that addresses copying an entire database including the sql server logins.
I would like to import the ENTIRE database from one (2005) server to another(2005) using the SSIS Transfer SQL Object task (not just sprocs,tables,views and functions). I have figured out how to pull the tables,views,sprocs and functions ... by using an execute sql task to drop these objects. But I cannot get this to work for users since the user dbo cannot be dropped and guest can only be disabled. I am creating a new database (this is the database where the sql objects will be copied to) via management studio to test this. There has to ba a way to get this working ... Microsoft must have published some sort of KB article on this task or a Script Task using SMO object calls. If need be I can drop the entire database on the target machine and have SSIS recreate it.
The only reason I'm willing to take a risk with SSIS rather than backup and restore is because of time constraints (I assume the SSIS task is faster) and backup storage administration.
declare @name varchar(200) declare @object varchar(200) DECLARE object_cursor CURSOR READ_ONLY FORWARD_ONLY FOR select table_name,table_type from INFORMATION_SCHEMA.TABLES union Select name,'SPROC' table_type from sys.procedures Where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%' union select name,'FN' from sys.objects where type_desc like '%FUNCTION' OPEN object_cursor FETCH NEXT FROM object_cursor INTO @name,@object IF @@FETCH_STATUS <> 0 PRINT ' <<None>>' WHILE @@FETCH_STATUS = 0 BEGIN if @object = 'BASE TABLE' begin exec ('drop table ' + @name) end else if @object = 'VIEW' begin exec ('drop view ' + @name) end else if @object = 'SPROC' begin exec ('drop procedure ' + @name) end else if @object = 'FN' begin exec ('drop function ' + @name) end FETCH NEXT FROM object_cursor INTO @name,@object END CLOSE object_cursor DEALLOCATE object_cursor
