Hi,
I am going to migrate a db from server A to server B. The login information
of the application are using SQL server authenication. From SQL help that
user id and password can be export and move from server A to server B as
following:
-- Server A
SELECT CONVERT(VARBINARY(32), password)
FROM syslogins
WHERE name = 'Margaret'
--Results
0x2131214A212B57304F5A552A3D513453
(1 row(s) affected)
-- Server B
EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
@.encryptopt = 'skip_encryption'
However, i have tried but the result is negative. What else i have missed so
that the above solution does not work? Or what should i do in order to move
the user id and password from server A to server B without changing the user
password? Thanks you very much!
Martin
You mention that this approach fails. My guess is that you have created the
login, but accessing the database fails because the SIDs don't match. Have a
look at this article: http://support.microsoft.com/kb/246133/ (SQL 2005:
http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx). The output is
login scripts that create logins with the original SID and password. You'll
have to drop the login first, or alternatively you can use
sp_change_users_login if as I suspect the login and user exist on the
destination server but just need mapping together.
HTH,
Paul Ibison
|||Hi
"Atenza" wrote:
> Hi,
> I am going to migrate a db from server A to server B. The login information
> of the application are using SQL server authenication. From SQL help that
> user id and password can be export and move from server A to server B as
> following:
> -- Server A
> SELECT CONVERT(VARBINARY(32), password)
> FROM syslogins
> WHERE name = 'Margaret'
> --Results
> ----
> 0x2131214A212B57304F5A552A3D513453
> (1 row(s) affected)
> -- Server B
> EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
> @.encryptopt = 'skip_encryption'
> However, i have tried but the result is negative. What else i have missed so
> that the above solution does not work? Or what should i do in order to move
> the user id and password from server A to server B without changing the user
> password? Thanks you very much!
> Martin
>
When moving logins you will need to match sids with the existing system
otherwise the restored database will have orphaned users
http://support.microsoft.com/kb/246133/ gives a procedure that will produce
the script you need to transfer the logins.
John
|||Thx all, i have tried the solution in those link, it works!!!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6D4D259D-E7AC-4910-9085-9D59D3DE66ED@.microsoft.com...
> Hi
> "Atenza" wrote:
> When moving logins you will need to match sids with the existing system
> otherwise the restored database will have orphaned users
> http://support.microsoft.com/kb/246133/ gives a procedure that will
> produce
> the script you need to transfer the logins.
> John
Showing posts with label login. Show all posts
Showing posts with label login. Show all posts
Friday, March 30, 2012
moving user id and password from one server to another server
Hi,
I am going to migrate a db from server A to server B. The login information
of the application are using SQL server authenication. From SQL help that
user id and password can be export and move from server A to server B as
following:
-- Server A
SELECT CONVERT(VARBINARY(32), password)
FROM syslogins
WHERE name = 'Margaret'
--Results
----
0x2131214A212B57304F5A552A3D513453
(1 row(s) affected)
-- Server B
EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
@.encryptopt = 'skip_encryption'
However, i have tried but the result is negative. What else i have missed so
that the above solution does not work? Or what should i do in order to move
the user id and password from server A to server B without changing the user
password? Thanks you very much!
MartinMartin
See this link
http://www.dbazine.com/sql/sql-articles/sharma3
regards
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:%23yhExk4pHHA.4196@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I am going to migrate a db from server A to server B. The login
> information of the application are using SQL server authenication. From
> SQL help that user id and password can be export and move from server A to
> server B as following:
> -- Server A
> SELECT CONVERT(VARBINARY(32), password)
> FROM syslogins
> WHERE name = 'Margaret'
> --Results
> ----
> 0x2131214A212B57304F5A552A3D513453
> (1 row(s) affected)
> -- Server B
> EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
> @.encryptopt = 'skip_encryption'
> However, i have tried but the result is negative. What else i have missed
> so that the above solution does not work? Or what should i do in order to
> move the user id and password from server A to server B without changing
> the user password? Thanks you very much!
> Martin
>|||You mention that this approach fails. My guess is that you have created the
login, but accessing the database fails because the SIDs don't match. Have a
look at this article: http://support.microsoft.com/kb/246133/ (SQL 2005:
http://blogs.msdn.com/lcris/archive.../03/567680.aspx). The output is
login scripts that create logins with the original SID and password. You'll
have to drop the login first, or alternatively you can use
sp_change_users_login if as I suspect the login and user exist on the
destination server but just need mapping together.
HTH,
Paul Ibison|||Hi
"Atenza" wrote:
> Hi,
> I am going to migrate a db from server A to server B. The login informatio
n
> of the application are using SQL server authenication. From SQL help that
> user id and password can be export and move from server A to server B as
> following:
> -- Server A
> SELECT CONVERT(VARBINARY(32), password)
> FROM syslogins
> WHERE name = 'Margaret'
> --Results
> ----
> 0x2131214A212B57304F5A552A3D513453
> (1 row(s) affected)
> -- Server B
> EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
> @.encryptopt = 'skip_encryption'
> However, i have tried but the result is negative. What else i have missed
so
> that the above solution does not work? Or what should i do in order to mov
e
> the user id and password from server A to server B without changing the us
er
> password? Thanks you very much!
> Martin
>
When moving logins you will need to match sids with the existing system
otherwise the restored database will have orphaned users
http://support.microsoft.com/kb/246133/ gives a procedure that will produce
the script you need to transfer the logins.
John|||Thx all, i have tried the solution in those link, it works!!!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6D4D259D-E7AC-4910-9085-9D59D3DE66ED@.microsoft.com...
> Hi
> "Atenza" wrote:
>
> When moving logins you will need to match sids with the existing system
> otherwise the restored database will have orphaned users
> http://support.microsoft.com/kb/246133/ gives a procedure that will
> produce
> the script you need to transfer the logins.
> Johnsql
I am going to migrate a db from server A to server B. The login information
of the application are using SQL server authenication. From SQL help that
user id and password can be export and move from server A to server B as
following:
-- Server A
SELECT CONVERT(VARBINARY(32), password)
FROM syslogins
WHERE name = 'Margaret'
--Results
----
0x2131214A212B57304F5A552A3D513453
(1 row(s) affected)
-- Server B
EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
@.encryptopt = 'skip_encryption'
However, i have tried but the result is negative. What else i have missed so
that the above solution does not work? Or what should i do in order to move
the user id and password from server A to server B without changing the user
password? Thanks you very much!
MartinMartin
See this link
http://www.dbazine.com/sql/sql-articles/sharma3
regards
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:%23yhExk4pHHA.4196@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I am going to migrate a db from server A to server B. The login
> information of the application are using SQL server authenication. From
> SQL help that user id and password can be export and move from server A to
> server B as following:
> -- Server A
> SELECT CONVERT(VARBINARY(32), password)
> FROM syslogins
> WHERE name = 'Margaret'
> --Results
> ----
> 0x2131214A212B57304F5A552A3D513453
> (1 row(s) affected)
> -- Server B
> EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
> @.encryptopt = 'skip_encryption'
> However, i have tried but the result is negative. What else i have missed
> so that the above solution does not work? Or what should i do in order to
> move the user id and password from server A to server B without changing
> the user password? Thanks you very much!
> Martin
>|||You mention that this approach fails. My guess is that you have created the
login, but accessing the database fails because the SIDs don't match. Have a
look at this article: http://support.microsoft.com/kb/246133/ (SQL 2005:
http://blogs.msdn.com/lcris/archive.../03/567680.aspx). The output is
login scripts that create logins with the original SID and password. You'll
have to drop the login first, or alternatively you can use
sp_change_users_login if as I suspect the login and user exist on the
destination server but just need mapping together.
HTH,
Paul Ibison|||Hi
"Atenza" wrote:
> Hi,
> I am going to migrate a db from server A to server B. The login informatio
n
> of the application are using SQL server authenication. From SQL help that
> user id and password can be export and move from server A to server B as
> following:
> -- Server A
> SELECT CONVERT(VARBINARY(32), password)
> FROM syslogins
> WHERE name = 'Margaret'
> --Results
> ----
> 0x2131214A212B57304F5A552A3D513453
> (1 row(s) affected)
> -- Server B
> EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
> @.encryptopt = 'skip_encryption'
> However, i have tried but the result is negative. What else i have missed
so
> that the above solution does not work? Or what should i do in order to mov
e
> the user id and password from server A to server B without changing the us
er
> password? Thanks you very much!
> Martin
>
When moving logins you will need to match sids with the existing system
otherwise the restored database will have orphaned users
http://support.microsoft.com/kb/246133/ gives a procedure that will produce
the script you need to transfer the logins.
John|||Thx all, i have tried the solution in those link, it works!!!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6D4D259D-E7AC-4910-9085-9D59D3DE66ED@.microsoft.com...
> Hi
> "Atenza" wrote:
>
> When moving logins you will need to match sids with the existing system
> otherwise the restored database will have orphaned users
> http://support.microsoft.com/kb/246133/ gives a procedure that will
> produce
> the script you need to transfer the logins.
> Johnsql
moving user id and password from one server to another server
Hi,
I am going to migrate a db from server A to server B. The login information
of the application are using SQL server authenication. From SQL help that
user id and password can be export and move from server A to server B as
following:
-- Server A
SELECT CONVERT(VARBINARY(32), password)
FROM syslogins
WHERE name = 'Margaret'
--Results
----
0x2131214A212B57304F5A552A3D513453
(1 row(s) affected)
-- Server B
EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
@.encryptopt = 'skip_encryption'
However, i have tried but the result is negative. What else i have missed so
that the above solution does not work? Or what should i do in order to move
the user id and password from server A to server B without changing the user
password? Thanks you very much!
MartinMartin
See this link
http://www.dbazine.com/sql/sql-articles/sharma3
regards
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:%23yhExk4pHHA.4196@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I am going to migrate a db from server A to server B. The login
> information of the application are using SQL server authenication. From
> SQL help that user id and password can be export and move from server A to
> server B as following:
> -- Server A
> SELECT CONVERT(VARBINARY(32), password)
> FROM syslogins
> WHERE name = 'Margaret'
> --Results
> ----
> 0x2131214A212B57304F5A552A3D513453
> (1 row(s) affected)
> -- Server B
> EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
> @.encryptopt = 'skip_encryption'
> However, i have tried but the result is negative. What else i have missed
> so that the above solution does not work? Or what should i do in order to
> move the user id and password from server A to server B without changing
> the user password? Thanks you very much!
> Martin
>|||You mention that this approach fails. My guess is that you have created the
login, but accessing the database fails because the SIDs don't match. Have a
look at this article: http://support.microsoft.com/kb/246133/ (SQL 2005:
http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx). The output is
login scripts that create logins with the original SID and password. You'll
have to drop the login first, or alternatively you can use
sp_change_users_login if as I suspect the login and user exist on the
destination server but just need mapping together.
HTH,
Paul Ibison|||Hi
"Atenza" wrote:
> Hi,
> I am going to migrate a db from server A to server B. The login information
> of the application are using SQL server authenication. From SQL help that
> user id and password can be export and move from server A to server B as
> following:
> -- Server A
> SELECT CONVERT(VARBINARY(32), password)
> FROM syslogins
> WHERE name = 'Margaret'
> --Results
> ----
> 0x2131214A212B57304F5A552A3D513453
> (1 row(s) affected)
> -- Server B
> EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
> @.encryptopt = 'skip_encryption'
> However, i have tried but the result is negative. What else i have missed so
> that the above solution does not work? Or what should i do in order to move
> the user id and password from server A to server B without changing the user
> password? Thanks you very much!
> Martin
>
When moving logins you will need to match sids with the existing system
otherwise the restored database will have orphaned users
http://support.microsoft.com/kb/246133/ gives a procedure that will produce
the script you need to transfer the logins.
John|||Thx all, i have tried the solution in those link, it works!!!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6D4D259D-E7AC-4910-9085-9D59D3DE66ED@.microsoft.com...
> Hi
> "Atenza" wrote:
>> Hi,
>> I am going to migrate a db from server A to server B. The login
>> information
>> of the application are using SQL server authenication. From SQL help that
>> user id and password can be export and move from server A to server B as
>> following:
>> -- Server A
>> SELECT CONVERT(VARBINARY(32), password)
>> FROM syslogins
>> WHERE name = 'Margaret'
>> --Results
>> ----
>> 0x2131214A212B57304F5A552A3D513453
>> (1 row(s) affected)
>> -- Server B
>> EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
>> @.encryptopt = 'skip_encryption'
>> However, i have tried but the result is negative. What else i have missed
>> so
>> that the above solution does not work? Or what should i do in order to
>> move
>> the user id and password from server A to server B without changing the
>> user
>> password? Thanks you very much!
>> Martin
> When moving logins you will need to match sids with the existing system
> otherwise the restored database will have orphaned users
> http://support.microsoft.com/kb/246133/ gives a procedure that will
> produce
> the script you need to transfer the logins.
> John
I am going to migrate a db from server A to server B. The login information
of the application are using SQL server authenication. From SQL help that
user id and password can be export and move from server A to server B as
following:
-- Server A
SELECT CONVERT(VARBINARY(32), password)
FROM syslogins
WHERE name = 'Margaret'
--Results
----
0x2131214A212B57304F5A552A3D513453
(1 row(s) affected)
-- Server B
EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
@.encryptopt = 'skip_encryption'
However, i have tried but the result is negative. What else i have missed so
that the above solution does not work? Or what should i do in order to move
the user id and password from server A to server B without changing the user
password? Thanks you very much!
MartinMartin
See this link
http://www.dbazine.com/sql/sql-articles/sharma3
regards
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:%23yhExk4pHHA.4196@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I am going to migrate a db from server A to server B. The login
> information of the application are using SQL server authenication. From
> SQL help that user id and password can be export and move from server A to
> server B as following:
> -- Server A
> SELECT CONVERT(VARBINARY(32), password)
> FROM syslogins
> WHERE name = 'Margaret'
> --Results
> ----
> 0x2131214A212B57304F5A552A3D513453
> (1 row(s) affected)
> -- Server B
> EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
> @.encryptopt = 'skip_encryption'
> However, i have tried but the result is negative. What else i have missed
> so that the above solution does not work? Or what should i do in order to
> move the user id and password from server A to server B without changing
> the user password? Thanks you very much!
> Martin
>|||You mention that this approach fails. My guess is that you have created the
login, but accessing the database fails because the SIDs don't match. Have a
look at this article: http://support.microsoft.com/kb/246133/ (SQL 2005:
http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx). The output is
login scripts that create logins with the original SID and password. You'll
have to drop the login first, or alternatively you can use
sp_change_users_login if as I suspect the login and user exist on the
destination server but just need mapping together.
HTH,
Paul Ibison|||Hi
"Atenza" wrote:
> Hi,
> I am going to migrate a db from server A to server B. The login information
> of the application are using SQL server authenication. From SQL help that
> user id and password can be export and move from server A to server B as
> following:
> -- Server A
> SELECT CONVERT(VARBINARY(32), password)
> FROM syslogins
> WHERE name = 'Margaret'
> --Results
> ----
> 0x2131214A212B57304F5A552A3D513453
> (1 row(s) affected)
> -- Server B
> EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
> @.encryptopt = 'skip_encryption'
> However, i have tried but the result is negative. What else i have missed so
> that the above solution does not work? Or what should i do in order to move
> the user id and password from server A to server B without changing the user
> password? Thanks you very much!
> Martin
>
When moving logins you will need to match sids with the existing system
otherwise the restored database will have orphaned users
http://support.microsoft.com/kb/246133/ gives a procedure that will produce
the script you need to transfer the logins.
John|||Thx all, i have tried the solution in those link, it works!!!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6D4D259D-E7AC-4910-9085-9D59D3DE66ED@.microsoft.com...
> Hi
> "Atenza" wrote:
>> Hi,
>> I am going to migrate a db from server A to server B. The login
>> information
>> of the application are using SQL server authenication. From SQL help that
>> user id and password can be export and move from server A to server B as
>> following:
>> -- Server A
>> SELECT CONVERT(VARBINARY(32), password)
>> FROM syslogins
>> WHERE name = 'Margaret'
>> --Results
>> ----
>> 0x2131214A212B57304F5A552A3D513453
>> (1 row(s) affected)
>> -- Server B
>> EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
>> @.encryptopt = 'skip_encryption'
>> However, i have tried but the result is negative. What else i have missed
>> so
>> that the above solution does not work? Or what should i do in order to
>> move
>> the user id and password from server A to server B without changing the
>> user
>> password? Thanks you very much!
>> Martin
> When moving logins you will need to match sids with the existing system
> otherwise the restored database will have orphaned users
> http://support.microsoft.com/kb/246133/ gives a procedure that will
> produce
> the script you need to transfer the logins.
> John
Wednesday, March 28, 2012
Moving to hosted dedicated server - Cannot open database requested in login
Hello all,
I hope someone can help with my current issue as i have now spent the last 8hours trying to solve it! Here goes...
I am currently moving all my websites from our in house set up to a FASTHOSTSdedicated server. Our in house set up consists of separate server for weband data and works well even with my recent foray into .Net sites.
I have just moved the most recent of my .Net sites(http://www.alfadealerads.co.uk/home.aspx) to the FASTHOSTS server and cannotget the site to connect to the database (this has also been installed on theFASTHOSTS server). One of the pages that connects to the DB ishttp://www.alfadealerads.co.uk/links.aspx, where i am getting the error..
System.Data.SqlClient.SqlException: Cannot open database requested in login'MasseratiChauffeurDrive'. Login fails.
Login failed for user 'lateral'.
This DB has been restored from back up from the original DB. I haverecreated the user name on the new SQL server and added it to the new DBalso. I am using a webconfig to connect to the DB, the exact same onethat works on my current live server(http://www.maseratichauffeurdrive.com/home.aspx) only with the obvious changeto the SQL server name.
The only difference i can see in the 2 setups is that the new one has both thewebserver and the SQL server on the one machine and that machine uses domains.
My current in house setup does not use domains and as mentioned before web anddate are 2 separate servers.
Can anyone help? Or even understand what the hell i have written? lol
Thanks in advance
I know the backup and restore wizard can restore everything including login so the question is why do you have to recreate the login after the restore. I would do a fresh restore and delete the current database and try to login without any changes. Hope this helps.|||
TheFIsh wrote:
System.Data.SqlClient.SqlException: Cannot open database requested in login'MasseratiChauffeurDrive'. Login fails.
Login failed for user 'lateral'.
This DB has been restored from back up from the original DB. I haverecreated the user name on the new SQL server and added it to the new DBalso. I am using a webconfig to connect to the DB, the exact same onethat works on my current live server(http://www.maseratichauffeurdrive.com/home.aspx) only with the obvious changeto the SQL server name.
When you created the new login account, did you check the "SQL ServerAuthentication" radio button instead of leaving it on the default"Windows Authentication"?
|||Thanks for the feedback.
When you created the new login account, did you check the "SQL ServerAuthentication" radio button instead of leaving it on the default"Windows Authentication"?
Yes i checked the SQL server authentication and specified a password. Still not solved.
Cheers,
Stephen
Friday, March 9, 2012
Moving Servers (membership and login question)
We have succesfully moved SQL server to a new machine and this same machine will also be running IIS 5.1 to host our intranet. We will be doing the actual developing on other machines and publishing the site to the new machine.
Now, when I went to test the site, it all comes up just fine, but logging in is a problem. What do I need to do to configure the new SQL server to accept new members, logins, etc.
Oh, and actually membership is still on the "old" SQL.
When I tried to login, I get this:
Server Error in '/' Application.
Login failed for user ''. The user is not associated with a trusted SQL Server connection.
Thanks for the information.
hi..
what i feel of your information is that you shoul consider some issues :
1- the new sql you ahve installed may be in windows mode and you are truing to connect as a sql user.
solution: set your sql server security to mixed mod.
2-the logins you've made in previus sql server doesn't exist in new installation..
each database user in sql server , has a trusted login name.
for creating a user , you must biuld a "sql login" and then "db user"
these may solve your problem..
|||Thanks for the information. I am a "one-person" shop doing everything here from developing to SQL database administration and I an a new one to SQL.
I did change the mode to Mixed and it works fine.
Thanks again!
Subscribe to:
Posts (Atom)