| Author |
Topic |
|
DavidRhodes
Starting Member
40 Posts |
 Posted - 2004-03-03 : 07:49:29
|
Hi, i've got a couple of problems that i'm stumped on, check all the MS kb's but can't find a solution.I have a server with a default instance of MSDE installed along with a named instance. - Using EM from my desktop I can connect to the default instance via IP Address but not to the named instance via IP\INSTANCENAME. I've already changed the named instance to listen on port 1433 but still no luck?! Both instances were installed with the same MSDE ini file with the only difference of an instance name name on one of the installs.
- The second problem is that I can't start SQL Server Agent on the default instance yet can on the named instance, any attempt to start it return the error
quote:
SQLServerAgent could not be started (reason: Unable to connect to server '(local)'; SQLServerAgent cannot start).
Thanks in advance  |
|
|
DavidRhodes
Starting Member
40 Posts |
Posted - 2004-03-03 : 11:09:27
|
| I just found this post and found that my named instance is using port 1134. I can't change it to 1433 can I? because the default instance is using that port. I presume this is why I can't connect to this instance remotely. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-03 : 16:55:01
|
| The named instance can not listen on the same port as the default instance. To configure your client to talk to the specified port, open up regedit and go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectToIf ConnectTo key doesn't exist, create one. Then add a string value. The name of it can be the name of the server\instancename or an alias. Then the value data will be:DBMSSOCN, <serverName>, <portNumber>So for example if your named instance were TARA and the server name was Server1 and the port that TARA is listening on is 1134, I would create a string value named Server1\Tara. The data value would be DBMSSOCN,Server1,1134. You could also change Server1\Tara for the name to an alias and the alias is what you would refer to when connecting. The important part is the data value.Tara |
|
|
|
DavidRhodes
Starting Member
40 Posts |
Posted - 2004-03-03 : 18:01:13
|
Thank you  I can't believe that isn't documented anywhere! Or if it is I couldn't find it!Any ideas on my second problem anyone? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-03 : 18:03:55
|
| Check the account that the SQLSERVERAGENT service is logging on with. To do this, log onto the database server using that account. Or is it using the local system account? If so, change it to a domain account that has local admin privileges.Tara |
|
|
|
DavidRhodes
Starting Member
40 Posts |
Posted - 2004-03-03 : 18:30:39
|
| Is that the "Service startup account" under the general tab when viewing properties of Sql Server Agent? If so, it is greyed out, presumably because i'm remotely connected. Will I have to do it with osql on the server? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-03 : 18:37:17
|
| Yes that is where you can view it. You can also view if you go to the database server, go to control panel, then to administrative tools, then to services. Then double click on the service and go to logon tab. I always go to the service to view the information rather than looking at it using any SQL tool.Tara |
|
|
|
DavidRhodes
Starting Member
40 Posts |
Posted - 2004-03-03 : 18:43:01
|
| I tried running it under the Administrator account but still get the same error |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-03 : 18:50:37
|
| http://support.microsoft.com/default.aspx?scid=kb;en-us;237604So was the BUILTIN\Administrators group removed from SQL Server or sysadmin privilege removed from that group?Tara |
|
|
|
DavidRhodes
Starting Member
40 Posts |
Posted - 2004-03-04 : 04:17:10
|
| Doh!What I did the other day was export a db from one server to this one and included all the logins by mistake. I must have deleted the BUILTIN\Administrators group then.Thank you. All working now |
|
|
|
DavidRhodes
Starting Member
40 Posts |
Posted - 2004-05-02 : 14:29:24
|
quote:
Originally posted by tduggan
The named instance can not listen on the same port as the default instance. To configure your client to talk to the specified port, open up regedit and go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectToIf ConnectTo key doesn't exist, create one. Then add a string value. The name of it can be the name of the server\instancename or an alias. Then the value data will be:DBMSSOCN, <serverName>, <portNumber>So for example if your named instance were TARA and the server name was Server1 and the port that TARA is listening on is 1134, I would create a string value named Server1\Tara. The data value would be DBMSSOCN,Server1,1134. You could also change Server1\Tara for the name to an alias and the alias is what you would refer to when connecting. The important part is the data value.Tara
I'm setting up a web server that will connect to an named instance of SQL Server on another machine, how would I do that? Obviously the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer key isn't there as SQL Server is not installed on the server hosting the website |
|
|
|
DavidRhodes
Starting Member
40 Posts |
Posted - 2004-05-02 : 16:55:32
|
| I added the key and it worked, why is this not documented by MS!!! Grrr |
|
|
|
|
Connecting via EM + Agent problem