Can't connect after changing IP address

After changing from DHCP network address to a static IP address I can
not connect to my local database using ".\MyDBS" notation, howver
"127.0.0.1\MyDBS" works. Also, both work after restarting the SQL
service.
I am using the query analyzer to test success or failure.
My event logs show nothing and my SQL logs show no errors.
Any ideas on how to debug this further or solve it altogether?
Thank youHi Arron,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
From your information, after change the IP, in the Query Analyzer, you
could not connect to my local database using ".\MyDBS" while
"127.0.0.1\MyDBS" works. After you restart the SQL Server service, this
problem do not happen, right?
I want to go forward with some more information since I could not
re-produce this problem on my computer. Do you encouter any error message
when using ".\MYDBS" that indicating you cannot connect to the server? How
about using the '(local)'? If you do not restart the SQL Server service,
it will not connect to the server even after some time? If you connect from
another machine by Query Analyer, do you meet the same problem?
When a SQL Server is started, it will listen to the IP and the port of when
it is started. So, when you change the IP address, you should restart the
SQL Server service and then it will listen to the new IP address. For the
localhost and the 127.0.0.1 will be the local machine.
You could refer to the following article for the name resolution:
http://www.microsoft.com/technet/pr.../ewsa/ewsach08.
mspx#XSLTsection124121120120
Hope this helps. If you still have questions, please feel free to post
message here and I am glad to help.
Thanks.
Sincerely Yours
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Aaron,
I am reviewing you post and since we have not heard from you for some time
in the newsgroup, I wonder if you got answer from my answer. For any
question, please post your message here and we are glad to help.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

can't (re)start SQLServerAgent after changing its logon credentials

Since Copy Databas Wizard failed to copy anything from remote SS20005 (both are with SP2 3054) in any combination,
I changed SQLServerAgent(myinstance_name) to run under Network from Local System with the help of SQL Server Configuration Manager

For this change it was restarted, failed to start again and continues to be stopped giving
"SQLServerAgent could not be started (reason: Unable to connect to server '<mymachine_name>\<myinstance_name>'; SQLServerAgent cannot start)."

I changed SQL Server Agent (myinstance_name) back to Local Service, then to Local System and still can't start it.

So, my questions:
1)
What has happened and how can I start SQLServerAgent?

2)
Why cannot I start it under Network Service? since it apparently should access remote resources for CDW to copy from remote SS2005

This is on MS Windows XP SP2

PS
After few trials I amnaged to restart it again under Local System but still cannot change to any different one

If you change the account under which SQL Agent starts you need to make sure that the account has permissions within SQL server or else each tim eit tries to access SQL Server it will fail. Have a look at these:

http://support.microsoft.com/kb/283811

http://support.microsoft.com/kb/907557

|||

You can verify the permissions, windows group membership required, etc for the service accounts in the following books online topic:

Setting Up Windows Service Accounts

http://msdn2.microsoft.com/en-us/library/ms143504.aspx

The NT Rights and Privleges section has the local windows group membership requirements - that one often gets overlooked for some reason. The help topic is pretty thorough and does a good job of covering the requirements.

Also note that as the article states, using Network Service isn't recommended. A domain account can provide the same network access but in a more secure manner. Otherwise you open some holes - network service means you give permissions needed for the CDW to the machine account. And then other apps, services running under network service get the permissions as well as they would be operating under the machine account.

-Sue

Cannot view database properties

Hello all....

I am in the process of changing all of the collation to SQL_Latin1_General_CP1_CI_AS.

I have backed up all databases, and uninstalled SQL Server 2005. I then reinstalled SQL Server 2005, setting the server collation to SQL_Latin1_General_CP1_CI_AS.

The user databases have been restored and are accessible by all applications.

If I try to view the properties of any database (system or user), I get the following error:

*********************************************************************************************************************************

TITLE: Microsoft SQL Server Management Studio

Cannot show requested dialog.

ADDITIONAL INFORMATION:

Cannot show requested dialog. (SqlMgmt)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation. (Microsoft SQL Server, Error: 468)

****************************************************************************************************************************************

I have tried executing the "Alter Database [dbname] Collate SQL_Latin1_General_CP1_CI_AS" procedure, but that does seem to help. Does anyone have any ideas?

Thanks,

Forch

What is the collation or all the databases?

run select * from sys.databases.

How did you restore the databases, backup/restore, attach/dettach?

|||

Master is SQL_Latin1_General_CP_C1_AS, and the rest of the system dbs is Latin1_General_CI_AI. The user dbs are evenly divided between the 2.

I can't remember how I restored.... it was probably backup / restore.

Thanks,

Forch

|||

Hi Forch,

Probably the backed up databases have the old collation, but the SQL server system databases have the new collation.

You can do:

ALTER RESTORED_DATABASE { COLLATE SQL_Latin1_General_CP1_CI_AScollation_name }

to change the collation of the restored databases to the new collation

Cheers

|||Have you tried to access the database from the query analyzer too?

Cannot view database properties

Hello all....

I am in the process of changing all of the collation to SQL_Latin1_General_CP1_CI_AS.

I have backed up all databases, and uninstalled SQL Server 2005. I then reinstalled SQL Server 2005, setting the server collation to SQL_Latin1_General_CP1_CI_AS.

The user databases have been restored and are accessible by all applications.

If I try to view the properties of any database (system or user), I get the following error:

*********************************************************************************************************************************

TITLE: Microsoft SQL Server Management Studio

Cannot show requested dialog.

ADDITIONAL INFORMATION:

Cannot show requested dialog. (SqlMgmt)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation. (Microsoft SQL Server, Error: 468)

****************************************************************************************************************************************

I have tried executing the "Alter Database [dbname] Collate SQL_Latin1_General_CP1_CI_AS" procedure, but that does seem to help. Does anyone have any ideas?

Thanks,

Forch

What is the collation or all the databases?

run select * from sys.databases.

How did you restore the databases, backup/restore, attach/dettach?

|||

Master is SQL_Latin1_General_CP_C1_AS, and the rest of the system dbs is Latin1_General_CI_AI. The user dbs are evenly divided between the 2.

I can't remember how I restored.... it was probably backup / restore.

Thanks,

Forch

|||

Hi Forch,

Probably the backed up databases have the old collation, but the SQL server system databases have the new collation.

You can do:

ALTER RESTORED_DATABASE { COLLATE SQL_Latin1_General_CP1_CI_AScollation_name }

to change the collation of the restored databases to the new collation

Cheers

|||Have you tried to access the database from the query analyzer too?

Cannot run SQLServerAgent jobs after changing SA password

I am new to SQL Server 2000. When I set a password for SA all the SQLServerAgent jobs fail with a login error and will not start. I have tried to change the SQLAgent service login via the Control Panel/Services, but it will not accept SA as a valid login, when I browse logins, only the NT logins seem to appear. The jobs are owned by my normal login and not by SA.

This is probably a simple change, but I am going round in circles trying to locate the 'how to' documentation.SQLServerAgent service needs to run under a Windows account...|||Check your SQL Server connection settings in SQL Server Agent properties (Windows Auth. or SQL server Auth.).|||Thanks. I did not see the SQL agent connection properties with a login and password. All is now well.