I have to set up mirroring for a web application. The web application is designed to connect to Sql Server using Sql Server authentication. The system configuration includes 3 instances of sql server running on different boxes with one of those instances being a witness as we want to support automatic failover. All instances of Sql Server on running in the same domain. My question is what are my options for authentication between the Sql Server instances? Am I restricted to using Sql Server authentication because that is what the web app uses? The web app has a login created on the principal server with rights to access only the appropriate database.
I want to set up two servers so that users on Server_1 can run stored procedures on Server_2.
Server_1 is SQL Server 7.0 using NT and standard security, while Server_2 is 6.5 using standard security.
I want all users on Server1 mapped onto one login ('RemoteLogin') on Server2, with no password checking. And I'm only setting up the servers as Remote Servers rather than Linked.
would achieve this. Unfortunately it only works for the users who connect under standard security. Those who gain access via NT logins, get an error message:
Server: Msg 4002, Level 14, State 1, Line 1 Login failed
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.
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.)
I need to copy 80 logins within the same SQL Server (7.0 SP1) from 80 "old" logins that I'll delete later. I clearly need to maintain all the security options for the new logins. Is there a way to do this, adding the logins with the new name and granting all security options? Is there a script that will do this task or can somebody help me in doing that? Thank you.
I have two servers (server1, server2). I was trying to access the data from server1 to server2. I linked the servers and set up the option for data access. when I run the following command from server1: sp_remoteoption 'server2', 'sa', 'sa', TRUSTED, TRUE
It gives me the following error. How to handle this.
Server: Msg 15185, Level 16, State 1, Line 0 There is no remote user 'sa' mapped to local user 'sa' from the remote server 'server2'.
I have a server that was recently upgraded from SQL 6.5 to 7.0 that contains almost 700 logins using standard security. Is there a limit to the number of logins that SQL will host? The logins/users have all been added using a GUI within an application and does not support NT authentication. Does this cause any known problems in SQL 7? When I right click on the database and go to properties / permissions MMC gets hung up. Also one of the systems analysts is convinced that SQL is dropping permissions (I disagree to this). Any one have any thoughts or experience with large numbers of logins / users?
with sql 2005 1)I create a windows login in my home pc (win xp home sp2) and i can connect server with ssms this new user
but in my office (win xp pro sp2) create a windows login (it is a windows user also) and when try to connect with ssms take error 18456 (user name and pass is true!!!)
2)in my office pc i create a sql login which has no role. but this user can create users and dbs. but in my home pc, user has no role cant do thats...:eek:
Hi everyone,I am new to this forum.I hope some body will help me in this,I am trying this for the past 6hrs. on my server there are some hundreds of sql logins are there,I need to identify the logins which doesn't associated with either any databases or dbroles,server roles.Later i need to delete those logins.Can any body help me in this.
I want to generate script for users and their associated permissions on the xyz database and at the same time I want to generate script for associated logins and their roles over the xyz database.
We created web site which has NT Authentication....
Using service account SERVICE ACCOUNT WEBGROUP This has WEBGROUP_USERS
WEBGROUP_USERS Tracey John
When myself logs in... Connect To SQL i see
in sysprocesses WEBGROUP
Is there a way to pass TRACEY to the process rather than having SQL Login set up..
Reason is if someone adds themselves to this group in AD they could have access to our applications im trying to figure out how to not make AD drive the access to SQL.
Hello,I'm currently developing an access application which uses sql server as abackend.What i'm uncertain about, is how i should setup security without creatingtoo many sql server logins. I'm convinced that i should limit access to sqlserver, otherwise people can get in with other means than my application(e.g. odbc, sem, etc.).I've setup sql server in mixed mode and created a login for my applicationto connect to a database.So, what is the best way to implement tight security, but still be able tolog the current user and hostname? If a separate table is needed to createdusers and passwords, please tell me how to use it, i've never done thatapproach.Thnx------------------------------------------------------This mailbox protected from junk email by MailFrontier Desktopfrom MailFrontier, Inc. http://info.mailfrontier.com
On a huge Sql-Server 7 installation, we have various client applicationsdistributed along the Lan, accessing one main database.Each application accesses the db using one out of around ten logins. Most ofthem, have only DBDataReader right on the db, as they are consultationconsolles only.In order to monitor db usage, the customer requires some kind of log of useraccess.My need, mainly, is to INSERT a record into a log table, recording Date,Time, Login, Host of each access.But, and this is the problem, the job has to be done by the server itself,not by each client, because of various reasons:1) we don't like to increase rights of logins2) we don't plan to change anything in our custom client application3) few of those client applications have been developed by foreignsuppliers, so we cannot change them.My question is: does it exist any kind of authentication LOG, which I canwork on?Or, is it possible to activate a kind of TRIGGER, reacting on loginauthentication?Thanks in advanceAlberto
How can I get a list of all SQL server logins that are on a given sqlserver? What sql query would get me that? Or is there an sp thatgives me that. Also, if it could list what db they have rights tothat would be great also.
I am confused about windows account, win dimain account, sql loginaccoun and sql database user account.... can you give me a freshexample to show the exact relationship between them and how they worktogether?
we have nearly 1 thousand logins are there. out of thousand only 8 persons are directly interacting with sql server . remaining are accessing the databases through application only. my query is every one is having sysadmin permissions . if i remove the permissions to them, they can't able access the databases.i am new to the organization.perviously no one was there to maintain the server .i don't know how to resolve this problem.my manager asked me to do something for this one.
How can I write a script to generate the list of SQL users that have sysadmin privileges. I would also like to do the same for the windows accounts as well.
I'm monitoring 2005 while running our application by looking at
sys.dm_os_performance_counters, general statistics, logins/sec.
I began testing a .NET 2 application, and logins/sec went from 63 to 11,433 in 30 minutes. Even though I have closed the application, it is still showing 11,449 - the only thing that's happened in the last hour is that I've run some queries from QA. is this login number cumulative, or is it an actual reflection of logins/sec? How on earth could that many logins be happening when no one else can even get to that server?
Hi, I'm relatively new to the admin side of websites and DBs. In the past, with hosted environments I've been used to just using SQL logins to connect to the DB from a web app, but for something I'm delevoping at the moment I have control over the DB and web server and was wondering if using a trusted connection would be better. After reading around it seems that Microsoft warns against using mixed mode authentication because of clear text passwords, the DB I'm working again currently isn't in mixed mode. So far I have set up the application to run in its own application pool under a limited user account that also has limited access to the DB. Though like this every new application that needs access to a different DB would require a different pool. What I'm asking essentially is what's the best way of going about things; set up a new pool and user for each app that requires it, using a trusted connection or turn on mixed mode authentication on on the DB. Or a mixture of the two?
Folks, I need a help here. We are running our production database on SQL Server 7.0. We are in a process of switching the production database server to another server hardware. I was wondering, How can we copy the Logins/Passwords from my old server to the new one. Well this is very important to have all the old logins and passwords on the new server to have anybody login to it. I know, Doing Export will copy the logins. But would it allow the same old password to be on the new server?
I have created 2 SQL logins for use in connecting to an SQL server database (v7) from my web server. My idea is that one login (WebAdmin) would be assigned the db_owner role and one login (WebPublic) db_datareader and db_datawriter roles.
However, I have run into a problem. Only one of the logins works. The other generates an "Invalid object name" error. The login that works is the login that is listed as the owner in the SQL Server Enterpise Manager Tables pane. Even if the other login has a db_owner role in the user properties box.
Can anyone tell me what is going on?
My code looks something like this:
<% Set CN=Server.CreateObject("ADODB.Connection")
CN.Open "Driver={SQL Server};SERVER=<ip address>;UID=<either WebAdmin or WebPublic>;PWD=<appropriate password>
Response.Write "Connection Opened!<BR>" [this part works in both cases]
Set RS=Server.CreateObject("ADODB.Recordset") RS.Open "Select * from map_gallery_primary_geographies", CN,3, 3
[this generates the "Invalid object name" error for the login that is not listed as owner in the Enterprise Manager]
Response.Write "Value of the first field's record: " & RS(0)
Does anyone have a sample SQL DMO script to transfer SQL Server logins from one server to another including the passwords ? any pointers will be greatly appreciated
