Link to home
Start Free TrialLog in
Avatar of motioneye
motioneyeFlag for Singapore

asked on

convert of windows logisn to sql logins

Guys,
I have a situation where  I need to migrate / copy existing  logins from windows authentication to - sql authentication. Every windows  Logins  has specific server role, database role and attach to  schema. So  in order to copy this login and again create them as sql logins, do you guys able to recommend a method of doing this ? currently this sql server has around 2000 actives windows login that I must convert them to sql authentication.
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

>> currently this sql server has around 2000 actives windows login that I must convert them to sql authentication

Wondering why you have 2000 Active Windows Login to a SQL Server as it would be difficult to manage the permissions and authorizations.
If you have a need for these many users, then you should have created appropriate Active Directory Groups and categorized each and every users to a particular group instead of directly creating these many users in SQL Server.
Kindly try implementing this for better management of your SQL Logins in future.

If this can't be implemented for any valid reasons, please follow a below process to convert the Windows logins to SQL Logins:
1. Get the list of logins along with its privileges using the script below..
https://gallery.technet.microsoft.com/scriptcenter/Get-logins-databases-816f66b2#content
2. For individual logins received, we need to get the appropriate rights or permissions assigned by running the below script..
https://gallery.technet.microsoft.com/Extract-Database-dfa53d5a
3. Now create the New SQL Login for every windows login.
4. Assign the privileges held by the equivalent Windows login to SQL Login.
5. Drop the Windows Login after careful testing of SQL Login.
Hi,

I like the sp_help_revlogins script that I've downloaded from Microsoft on several occasions.

I will help you script out the logins.

But you'll be creating them with different sids. You will then need to match these new logins to users inside each database, so I agree with Raja's comments above that this seems a fairly difficult way to manage things.

Regards
  David
If memory serves, windows based login do not use sids and would apply.
So you really can not convert Windows logins to SQL logins.

As raja pointed out, creating AD groups that are then identified with their desired role on the SQL server.

You Dan then try to associate the Windows group login, to refer as a specific SQL user (user mapping if not mistaken......

The sp_help_revlogin as David pointed out is a way to transfer server sevurity accounts on which database/security accounts rely.

The complexity you run into deals with how the DBS are accessed.
Currently, your system might be using integrated security whereby each user is authorized based on the Windows login, changing to SQL level logins could present its own issues on how users would provide their credentials.

In SQL based logins, commonly, the application runs with its own SQL login (unknown to the user) and the application has its own user table where usernames, passwords and rights are managed, by the application.
Worse, with SQL logins you need to provide explicit credentials, including the password. In converting from Windows accounts you have no way to know the "original" password. To keep some wecurity you need to set the password of each account different, and how do you want to mange that then for the applications trying 5o connect?
Avatar of motioneye

ASKER

Hi all,
In that case, I will create new logins and pass a new login to customer, however I need to ensure that the login must have identical sql role  and its database permissions remain identical. I'm not really worry about the password as I will generate a random password for each users and will send to them  in proper method.
>>  I need to ensure that the login must have identical sql role  and its database permissions remain identical

Yes, the scripts I've shared above should help you out to create identical logins..
While this question may seem off topic, it is not.
The change from windows login (Integrated security based access) to sql login.

What is the setup, do users each have excel, access front end, SSMS and they directly access the SQL server?

Or do they have a native windows application?

Depending on what is being used, the change could involve additional/extensive modification..

on the face of your request, where you are trying toconvert 2000 windows based accounts, to 2000 sql login would ...

Commonly, there are no 2000 users that need direct access to the sql server.

in the trusted integrated security model, the user using IE accesses a web server where their session gets set to run with their credentials. then when the session access the data in a database the same credentials (integrated ) are passed from the web based application to the SQL server for purposes of authorization/access.
if those are met, the sql server accepts the request and provides the response. The SQL server is the enforcer on the rights of what each users can access, the application has transparent enforcement that limits which pages/options the user can access.


Point being if this is a web based application, or a client server (windows application front end) commonly, when using sql logins, the web application and the client based application always use a single pre-defined sql login.
To access the application/get it started, the user is prompted for a separate login. or if using integrated security, the application is the sole enforcer of what interfaces/options the user has as all requests from the application to the sql server are made using a single sql login.

Prompting for a user to enter their sql login credentials, that the application would then need to switch and use in the session...........

have not seen it done.

sql server, DB to which access is need (backend for the application) which includes a user table with related where access, permissions, etc. are defined and on whose basis the application determines which options are available to which users.
ASKER CERTIFIED SOLUTION
Avatar of motioneye
motioneye
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dfke
dfke

Hi,

Like Raja stated earlier, the easiest way is to add the AD groups as a new SQL login, add their appropriate roles and schemas and you should be set. Any member of those AD groups can now login to SQL Server and use your database instance.

Cheers
currently this sql server has around 2000 actives windows login
>> I have no way around, instead creating a users with sql logins authentication

In my honest opinion, creating 2000 SQL Logins is a terrible idea and bad practice in terms of maintainability.
May be if you have shared your current situation in more detail(if shareable) we could have guided you better..

>> the easiest way is to add the AD groups as a new SQL login

dfke, to correct your statement a little bit AD group would need to be assigned as a Windows login(not a SQL Login) so that members in that particular AD group can login to SQL Server without any issues..