SQL problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL problem

Author

Topic

Dzha2000
Starting Member

8 Posts

Posted - 2008-04-17 : 13:35:41

Hi Guys.

I have a problem with SQL. I have two servers running 2 separate domains (let's call them old server and new server). I'm trying to migrate users from old to new server. I have a user who need access to SQL on old server (which will not be migrated to new server), but him being on new server. How can I do that without establishing two way trust between domains? was thinking through about SQL server authentication, but that didn't work either.

if anyone can give me some pointer I would really appreciate it.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-04-17 : 13:55:47

What are you using to migrate the users? Windows or sql authentication? If sql authentication, use the following. Run it on the old server. It'll generate a script of logins that you'll need to run on the new server. If Windows authentication be sure there are domain accounts on each domain.

----- Begin Script, Create sp_help_revlogin procedure -----

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)

IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
----- End Script -----

Terry

Dzha2000
Starting Member

8 Posts

Posted - 2008-04-17 : 14:00:41

I think I didn't make myself clear, sorry. He has new account in AD on new server. This account needs to be able to connect to the SQL on the old server.

both domains and AD I'm trying to keep separate as much as possible. what I'm trying to achieve in the end is having new server handling all the users as such, and the old server SQL and some licensing software.

Dzha2000
Starting Member

8 Posts

Posted - 2008-04-17 : 14:04:18

oh and another thing. I'm have no knowledge of SQL. I was thinking about windows authentication but I think you need to establish trust between the domains to do that which I'm trying to avoid. I was wondering if it would be easier for me (not knowing anything about SQL) to use SQL authentication.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-04-17 : 14:14:25

I'm not an expert on AD. Can you grant domain access to the new account on the old server (old domain)? It sounds doable but I know as much about AD as you do SQL server I guess! Anyway, you can give SQL authentication but somehow the person is still going to need to log into the old domain to access the database.

Terry

Dzha2000
Starting Member

8 Posts

Posted - 2008-04-17 : 14:29:04

ok thank you for your opinion. I guess there is no way to do what I need to do without domain trust.

do you by any chance know if you are domain user you by default get access to SQL database or not?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-17 : 14:33:10

Domain users do not get access to SQL Server by default. That would hardly be secure.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Dzha2000
Starting Member

8 Posts

Posted - 2008-04-17 : 14:40:14

that's what I think too, but just browsing through the settings on both AD and SQL I don't see any user accounts in SQL which will allow domain users to gain access to SQL, or maybe I just don't get something.

where can I check what users from AD can gain access to SQL database?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-17 : 14:43:55

It's not that I think this is the case. I know it to be the case.

You can check the logins in SQL Server in Management Studio to figure who has access. If you see AD groups, then you will need to check in AD who is a member of that group.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources