Renaming and migration Problems

Hi, we have a Windows 2k3 server that has SQL Server 2000. we just
moved the Database over to this new server, then renamed the server and
assigned it the same IP as the old one. then we did the sp_dropserver
and ap_addserver script to cange the SQL server name. the problems that
we keep getting it that the client machines can't connect to the SQL
Server now. They can see the server and ping to it but can't access the
actual SQL databses. the error message that I get is that its "SQL
Server cannot be found or access denied". any ideas? we have been
trying to get his new server up and running but keep running into this
issue. are we missing any steps?
Thanks
Hello,
Did you restarted the SQL Server and SQL Agent service after issuing
SP_DROPServer and SP_ADDSERVER commands?
As well as did you gave the below command to add server
SP_ADDSERVER 'Newservername','LOCAL'
Try both and keep us posted..
Thanks
Hari
<rncota@.gmail.com> wrote in message
news:1168899609.093776.80630@.l53g2000cwa.googlegro ups.com...
> Hi, we have a Windows 2k3 server that has SQL Server 2000. we just
> moved the Database over to this new server, then renamed the server and
> assigned it the same IP as the old one. then we did the sp_dropserver
> and ap_addserver script to cange the SQL server name. the problems that
> we keep getting it that the client machines can't connect to the SQL
> Server now. They can see the server and ping to it but can't access the
> actual SQL databses. the error message that I get is that its "SQL
> Server cannot be found or access denied". any ideas? we have been
> trying to get his new server up and running but keep running into this
> issue. are we missing any steps?
> Thanks
>
|||Hi
"rncota@.gmail.com" wrote:

> Hi, we have a Windows 2k3 server that has SQL Server 2000. we just
> moved the Database over to this new server, then renamed the server and
> assigned it the same IP as the old one. then we did the sp_dropserver
> and ap_addserver script to cange the SQL server name. the problems that
> we keep getting it that the client machines can't connect to the SQL
> Server now. They can see the server and ping to it but can't access the
> actual SQL databses. the error message that I get is that its "SQL
> Server cannot be found or access denied". any ideas? we have been
> trying to get his new server up and running but keep running into this
> issue. are we missing any steps?
> Thanks
>
If you connect via query analyser on the machine itself and SELECT
@.@.SERVERNAME do you get the new name or the old one? If the old one check
that you are dropping the server correctly as described by
http://msdn2.microsoft.com/en-us/library/aa197071(SQL.80).aspx
You don't say if you transferred the logins as well as the database, see
http://support.microsoft.com/kb/314546 exec sp_change_users_login 'Report'
will show any orphaned users.
Have you tried connecting with the IP address? Check which protocols are
installed on the server and client and that the clients don't have any
aliases configured. If this is MSDE check that DISABLENETWORKPROTOCOLS was
not specified as on when installing.
John
|||Hari Prasad wrote:
> Hello,
> Did you restarted the SQL Server and SQL Agent service after issuing
> SP_DROPServer and SP_ADDSERVER commands?
> As well as did you gave the below command to add server
> SP_ADDSERVER 'Newservername','LOCAL'
> Try both and keep us posted..
> Thanks
> Hari
>
Yes, we used that process to change the internal name of the SQL
Server. We checked the name using Select @.@.SERVERNAME. also we only
have one instance running, the defualt one.
|||> If you connect via query analyser on the machine itself and SELECT
> @.@.SERVERNAME do you get the new name or the old one? If the old one check
> that you are dropping the server correctly as described by
> http://msdn2.microsoft.com/en-us/library/aa197071(SQL.80).aspx
> You don't say if you transferred the logins as well as the database, see
> http://support.microsoft.com/kb/314546 exec sp_change_users_login 'Report'
> will show any orphaned users.
> Have you tried connecting with the IP address? Check which protocols are
> installed on the server and client and that the clients don't have any
> aliases configured. If this is MSDE check that DISABLENETWORKPROTOCOLS was
> not specified as on when installing.
> John
Yes, we checked using Select @.@.SERVERNAME and the new server name comes
up. We also migrated the logins as well as DTS packages and jobs. When
I run exec sp_change_users_login 'Report' nothing comes up as being
orphaned.
I can ping the server using both the Name of the Server and the IP
address of the server through a command prompt from a client machine.
the problem comes up when i try to connect through applications that
access the databases. an example of how I test it was through and ODBC
connection that is required for one of our apps. When went in to test
the connection to it in the ODBC settings, it says that it cannot find
the specified server. I know the login infomation is correct and I know
the logins exisit.
Just to give more information to help figure out this problem, another
example is when we were testing our DTS packages on the SQL server,
anywhere that referenced the name to the SQL Server would come back
with an error telling us that the server could not be found. which is
odd because these packages are sitting inside that server. Very odd. I
feel like there is something missing some where, but we have done
everything by the book from what I can tell.
|||Also i just did a test, since we couldn't get the new SQL server
running properly we went back to the old server. the new server is up
but now running on a different name. When i test a ODBC connection to
the new server using a connection with the name is has now, the
connection works fine. seems to be some issue when we try to pass on
the name and IP of the old server to it.
|||rncota@.gmail.com wrote:
> Also i just did a test, since we couldn't get the new SQL server
> running properly we went back to the old server. the new server is up
> but now running on a different name. When i test a ODBC connection to
> the new server using a connection with the name is has now, the
> connection works fine. seems to be some issue when we try to pass on
> the name and IP of the old server to it.
>
Try this simple test:
1. Choose a login that you are attempting to connect with, one that
fails, i.e. login name "bob"
2. Open a Query Analyzer session, connect to your application database
as "sa"
3. EXEC sp_dropuser 'bob'
4. EXEC sp_adduser 'bob'
5. EXEC sp_addrolemember 'db_datareader', 'bob'
Now test a connection to the application database using the login 'bob'
- does it connect successfully?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||
> Try this simple test:
> 1. Choose a login that you are attempting to connect with, one that
> fails, i.e. login name "bob"
> 2. Open a Query Analyzer session, connect to your application database
> as "sa"
> 3. EXEC sp_dropuser 'bob'
> 4. EXEC sp_adduser 'bob'
> 5. EXEC sp_addrolemember 'db_datareader', 'bob'
> Now test a connection to the application database using the login 'bob'
> - does it connect successfully?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
I tried this test, it works fine when I try to connect to the server
with its current name but when We change the server name it stops
working.
|||Hi
"rncota@.gmail.com" wrote:

> Also i just did a test, since we couldn't get the new SQL server
> running properly we went back to the old server. the new server is up
> but now running on a different name. When i test a ODBC connection to
> the new server using a connection with the name is has now, the
> connection works fine. seems to be some issue when we try to pass on
> the name and IP of the old server to it.
>
By being able to connect to the server when the name was changed back means
that your protocols are ok. Did you check for aliases on the client? Have you
tried to connect using the IP? Can you connect using Query Analyser from the
client? When you changed the name of the server (for the second time) did you
drop the server and add it again?
John