Cannot connect to SQL Server 2005 Express with Management Studio Express

On my home machine without permanent network connections enabled, I cannot get the Management Studio connect to the database server. Always get this error:

Cannot connect ot MPLIAM\SQLEXPRESS

Additional Information:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, erro: 26 - Error Locating Server/Instance Specified)(Microsoft SQL Server)

I have used the SQL Surface Area Configuration Tool to reset the defaults to allow remote connections, stopped and restarted the server, but still get the same message.

Please help.

Hi Michael,

I think this is a duplicate of another post from you, but I'll answer here too.

You don't say if you're actually trying to connect remotely or not. If you're connecting remotely, the issue is likely your firewall is blocking the connection. Check out the FAQ at the top of this forum for a link to the article that discuss the complete steps to allow remote connections.

Mike

|||

Can you ping \\MPLIAM?

Try usingthe IP address rather than the server name.

|||

Sorry. I lost the other post.

I'm just trying to connect on my own local machine to the server which is installed on my own local machine.

I have McAffee anti-virus software installed. There's no telling what that monstrosity is doing to shut down things -- all in the line of 'enhanced security', of course.

I am just about at my wits end trying to get this thing to work. I neglected to mention that when I first installed the server and the management studio, at least the latter connected to the former. Something changed somewhere along the way so that now it doesnt work. I've checked the Service in the Control Panel and it seems to be running. I even stopped and restarted it several times with no success. I used the config manager to configure the 'surface area' (love that one) so that remote connections are enabled. Still no luck.

What to do next?

|||

ping mpliam

Pinging MPLIAM [169.254.101.152] with 32 bytes of data:

Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128

Ping statistics for 169.254.101.152:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms

Using Server Name: 169.254.101.152 in the Managment Studio

'cannot connect to database'

Any other ideas ?

|||

(a) check whether the service started or not from Configuration Manager Window

(b) Check whether the protocols are enabled or not from SQL Configuration Manager window

(c) Check which all are the client protocol enabled

Madhu

|||

In the other thread I had suggest trying a connection via SQLCmd, just to rule out any Management Studio weirdness, are you able to do that? To reiterate, open a command prompt and type the following:

SQLCmd -S .\SQLEXPRESS -E -Q "SELECT @.@.Version"

This should return version information about your server if it works. If it fails, please post back the complete error message. As a second step should you encounter failure, please check the error log after the attempted connection to see if there is additional informaton there. The error log should be at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG.

It's interesting that you say this worked initially but connectons started failing later. This would indeed suggest that "something else" is impacting this, but I've no idea what. In general I wouldn't expect any AV program to prevent connections to SQL, but we should probably test this. As a temprorary troubleshooting step, disable McAffe and then try your connection again. Regardless of the result, turn McAffe back on once you've completed the test, but let us know if that impacts anything.

Finally, could you copy the test from the "Server" text box in the Management Studio Connection dialog and paste it into your reply?

Mike

|||

Thanks for your response, Mike.

I apologize for taking so long to respond. In any case, when I attempted at the Command Prompt:

SQLCmd -S .\SQLEXPRESS -E -Q "SELECT @.@.Version"

I received the following message (preceeded by a bunch of dashed lines):

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

(1 rows affected)

The Error Log was quite extensive and, since I have been fooling with SQL Server quite a bit, I am uncertain which section pertains. But I interpret the above to indicate that the installation is working.

Here's a little bit of the most recent part of the Error Log:

2007-03-18 09:00:44.95 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2007-03-18 09:00:44.95 Server (c) 2005 Microsoft Corporation.
2007-03-18 09:00:44.95 Server All rights reserved.
2007-03-18 09:00:44.96 Server Server process ID is 552.
2007-03-18 09:00:44.96 Server Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2007-03-18 09:00:44.96 Server This instance of SQL Server last reported using a process ID of 236 at 3/17/2007 10:54:30 PM (local) 3/18/2007 5:54:30 AM (UTC). This is an informational message only; no user action is required.
2007-03-18 09:00:44.98 Server Registry startup parameters:
2007-03-18 09:00:44.98 Server -d c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2007-03-18 09:00:44.98 Server -e c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2007-03-18 09:00:44.98 Server -l c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2007-03-18 09:00:45.06 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2007-03-18 09:00:45.06 Server Detected 1 CPUs. This is an informational message; no user action is required.
2007-03-18 09:00:45.67 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2007-03-18 09:00:45.70 Server Database Mirroring Transport is disabled in the endpoint configuration.
2007-03-18 09:00:45.71 spid5s Starting up database 'master'.
2007-03-18 09:00:46.04 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
// .....

The Server Text Box from the Management Studio (if I understand your request) looks as follows:

Connect to Server

Server type: Database Engine (disabled)
Server name: MPLIAM\SQLEXPRESS
Authentication: Windows Atuhentication
User name: MBPLIAM\Michael B Pliam (disabled)
Password: (blank and disabled)

Attempting to connect results in:

Connect to Server
Cannot connect to MPLIAM\SQLEXPRESS.

Additional information:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

I will try to disable McAffee Virus Protection and get back to you if it makes any difference (Assuming that I can figure out how to disable it - it's sort of like getting rid of a bad cold.)

Thanks again for your help. Again, I wish to apologize for having lost this thread.

|||

You are correct that the output of SQLCmd indicates that SQL Express is working fine and can be accessed locally with no problems. This would suggest a specific problem with Management Studio. I don't see any problem with the information you supplied, but the error message indicates that the specific instance name you've supplied is incorrect.

Replace the value in the 'Server name' text box with .\SQLEXPRESS and see if that makes any difference. If this works, it would mean that your machine name is not actually MPLIAM. If it doesn't work, we may to punt this over to the tools guys to see if they have any ideas.

Mike

|||

Problem solved !

Using .\SQLEXPRESS instead of MPLIAM\SQLEXPRESS allowed the Management Studio to connect to the server.

I still do not understand what the problem was.

My 'host' machine is definitely 'MPLIAM'.

If I 'ping MPLIAM' from a Command Line prompt (as I believe I posted before), I receive the response:

Pinging MPLIAM [169.254.101.152] with 32 bytes of data:

Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128

Ping statistics for 169.254.101.152:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms

Further, MSDN provides C++ source code to determine the host machine name and volume. For those interested, see:

http://windowssdk.msdn.microsoft.com/en-us/library/ms738527.aspx

Running that program reveals

MPLIAM
3758647270

the host name and volume number respectively.

When Management Studio did connect (as noted above), the connection text box header has the following information:

Summary

.\SQLEXPRESS (SQL Server 9.0.1399 - MPLIAM\Michael B Pliam)
MPLIAM\SQLEXPRESS 5 Items(s)

Mike, I want to thank you for your patience in helping me to solve this problem. If my tone at times appeared intemperant, it was simply my frustration at being unable to get this program to work. As is usually the case, it was probably my fault that things were awry. I must have changed something without being aware of it. Once things go bad, I have a tendency to start changing everything in sight instead of cautiously exploring the nature of the problem.

Regards

|||

I'm glad that it's working. It's odd that the machine name is not being recognized, but if you're happy, I'm happy.

Mike

|||Hi,

I've been struggling with a similar problem. Wasn't able to connect remotely and everything was setup correctly on the Surface Area Configuration. I could connect locally though, but not remotely. Used the exact same Windows User each time, and no firewall in between client and server. The eventual resolution was something really simple and stupid on my part.

The SQL Server browser service wasn't running, all that I did was start it and suddenly I could connect remotely... which is strange because I've never had this issue with any other SQL server I've worked with...

anyway hope it helps...

Cannot connect to SQL Server 2005 Express with Management Studio Express

On my home machine without permanent network connections enabled, I cannot get the Management Studio connect to the database server. Always get this error:

Cannot connect ot MPLIAM\SQLEXPRESS

Additional Information:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, erro: 26 - Error Locating Server/Instance Specified)(Microsoft SQL Server)

I have used the SQL Surface Area Configuration Tool to reset the defaults to allow remote connections, stopped and restarted the server, but still get the same message.

Please help.

Hi Michael,

I think this is a duplicate of another post from you, but I'll answer here too.

You don't say if you're actually trying to connect remotely or not. If you're connecting remotely, the issue is likely your firewall is blocking the connection. Check out the FAQ at the top of this forum for a link to the article that discuss the complete steps to allow remote connections.

Mike

|||

Can you ping \\MPLIAM?

Try usingthe IP address rather than the server name.

|||

Sorry. I lost the other post.

I'm just trying to connect on my own local machine to the server which is installed on my own local machine.

I have McAffee anti-virus software installed. There's no telling what that monstrosity is doing to shut down things -- all in the line of 'enhanced security', of course.

I am just about at my wits end trying to get this thing to work. I neglected to mention that when I first installed the server and the management studio, at least the latter connected to the former. Something changed somewhere along the way so that now it doesnt work. I've checked the Service in the Control Panel and it seems to be running. I even stopped and restarted it several times with no success. I used the config manager to configure the 'surface area' (love that one) so that remote connections are enabled. Still no luck.

What to do next?

|||

ping mpliam

Pinging MPLIAM [169.254.101.152] with 32 bytes of data:

Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128

Ping statistics for 169.254.101.152:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms

Using Server Name: 169.254.101.152 in the Managment Studio

'cannot connect to database'

Any other ideas ?

|||

(a) check whether the service started or not from Configuration Manager Window

(b) Check whether the protocols are enabled or not from SQL Configuration Manager window

(c) Check which all are the client protocol enabled

Madhu

|||

In the other thread I had suggest trying a connection via SQLCmd, just to rule out any Management Studio weirdness, are you able to do that? To reiterate, open a command prompt and type the following:

SQLCmd -S .\SQLEXPRESS -E -Q "SELECT @.@.Version"

This should return version information about your server if it works. If it fails, please post back the complete error message. As a second step should you encounter failure, please check the error log after the attempted connection to see if there is additional informaton there. The error log should be at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG.

It's interesting that you say this worked initially but connectons started failing later. This would indeed suggest that "something else" is impacting this, but I've no idea what. In general I wouldn't expect any AV program to prevent connections to SQL, but we should probably test this. As a temprorary troubleshooting step, disable McAffe and then try your connection again. Regardless of the result, turn McAffe back on once you've completed the test, but let us know if that impacts anything.

Finally, could you copy the test from the "Server" text box in the Management Studio Connection dialog and paste it into your reply?

Mike

|||

Thanks for your response, Mike.

I apologize for taking so long to respond. In any case, when I attempted at the Command Prompt:

SQLCmd -S .\SQLEXPRESS -E -Q "SELECT @.@.Version"

I received the following message (preceeded by a bunch of dashed lines):

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

(1 rows affected)

The Error Log was quite extensive and, since I have been fooling with SQL Server quite a bit, I am uncertain which section pertains. But I interpret the above to indicate that the installation is working.

Here's a little bit of the most recent part of the Error Log:

2007-03-18 09:00:44.95 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2007-03-18 09:00:44.95 Server (c) 2005 Microsoft Corporation.
2007-03-18 09:00:44.95 Server All rights reserved.
2007-03-18 09:00:44.96 Server Server process ID is 552.
2007-03-18 09:00:44.96 Server Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2007-03-18 09:00:44.96 Server This instance of SQL Server last reported using a process ID of 236 at 3/17/2007 10:54:30 PM (local) 3/18/2007 5:54:30 AM (UTC). This is an informational message only; no user action is required.
2007-03-18 09:00:44.98 Server Registry startup parameters:
2007-03-18 09:00:44.98 Server -d c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2007-03-18 09:00:44.98 Server -e c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2007-03-18 09:00:44.98 Server -l c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2007-03-18 09:00:45.06 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2007-03-18 09:00:45.06 Server Detected 1 CPUs. This is an informational message; no user action is required.
2007-03-18 09:00:45.67 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2007-03-18 09:00:45.70 Server Database Mirroring Transport is disabled in the endpoint configuration.
2007-03-18 09:00:45.71 spid5s Starting up database 'master'.
2007-03-18 09:00:46.04 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
// .....

The Server Text Box from the Management Studio (if I understand your request) looks as follows:

Connect to Server

Server type: Database Engine (disabled)
Server name: MPLIAM\SQLEXPRESS
Authentication: Windows Atuhentication
User name: MBPLIAM\Michael B Pliam (disabled)
Password: (blank and disabled)

Attempting to connect results in:

Connect to Server
Cannot connect to MPLIAM\SQLEXPRESS.

Additional information:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

I will try to disable McAffee Virus Protection and get back to you if it makes any difference (Assuming that I can figure out how to disable it - it's sort of like getting rid of a bad cold.)

Thanks again for your help. Again, I wish to apologize for having lost this thread.

|||

You are correct that the output of SQLCmd indicates that SQL Express is working fine and can be accessed locally with no problems. This would suggest a specific problem with Management Studio. I don't see any problem with the information you supplied, but the error message indicates that the specific instance name you've supplied is incorrect.

Replace the value in the 'Server name' text box with .\SQLEXPRESS and see if that makes any difference. If this works, it would mean that your machine name is not actually MPLIAM. If it doesn't work, we may to punt this over to the tools guys to see if they have any ideas.

Mike

|||

Problem solved !

Using .\SQLEXPRESS instead of MPLIAM\SQLEXPRESS allowed the Management Studio to connect to the server.

I still do not understand what the problem was.

My 'host' machine is definitely 'MPLIAM'.

If I 'ping MPLIAM' from a Command Line prompt (as I believe I posted before), I receive the response:

Pinging MPLIAM [169.254.101.152] with 32 bytes of data:

Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128
Reply from 169.254.101.152: bytes=32 time<1ms TTL=128

Ping statistics for 169.254.101.152:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms

Further, MSDN provides C++ source code to determine the host machine name and volume. For those interested, see:

http://windowssdk.msdn.microsoft.com/en-us/library/ms738527.aspx

Running that program reveals

MPLIAM
3758647270

the host name and volume number respectively.

When Management Studio did connect (as noted above), the connection text box header has the following information:

Summary

.\SQLEXPRESS (SQL Server 9.0.1399 - MPLIAM\Michael B Pliam)
MPLIAM\SQLEXPRESS 5 Items(s)

Mike, I want to thank you for your patience in helping me to solve this problem. If my tone at times appeared intemperant, it was simply my frustration at being unable to get this program to work. As is usually the case, it was probably my fault that things were awry. I must have changed something without being aware of it. Once things go bad, I have a tendency to start changing everything in sight instead of cautiously exploring the nature of the problem.

Regards

|||

I'm glad that it's working. It's odd that the machine name is not being recognized, but if you're happy, I'm happy.

Mike

|||Hi,

I've been struggling with a similar problem. Wasn't able to connect remotely and everything was setup correctly on the Surface Area Configuration. I could connect locally though, but not remotely. Used the exact same Windows User each time, and no firewall in between client and server. The eventual resolution was something really simple and stupid on my part.

The SQL Server browser service wasn't running, all that I did was start it and suddenly I could connect remotely... which is strange because I've never had this issue with any other SQL server I've worked with...

anyway hope it helps...

Cannot connect to localhost using Windows Security Group as login

I have a test environment set up at home with a small windows server 2003 Active Directory domain. I created a windows security group on the dc and added myself and another user to the group. I then use this group as a login for SQL Server and add the login to the sysadmin server role. This works exactly as I would expect on the Windows Server computer (which is also the Domain Controller) regardless of which computer I use to connect to this Windows Server computer. However, when I set up the exact same login on the local sql server instance of my client laptop running windows xp pro, the I get an error message (Login failed for user MYDOMAIN\dgolds...) when I attempt to connect to the local instance of SQL server, even though I am able to connect to the instance on the domain controller with no problems using this same client laptop under the same user account. The only way I am able to connect locally on the laptop under this account (which does not have local admin privileges on any box, for testing purposes) is to add the user account separately as a login, rather than as part of a group. I'm curious as to why being a member of a security group that is part of the sysadmin server role does not allow me to connect to the local instance of SQL Server on this client laptop. The windows server computer is running SQL2k5 Enterprise, and the client laptop is running SQL2k5 developer. Any insight into this would be appreciated.

Thanks,

Dave

Let's focus on the laptop for a moment, becuase that seems to be where your question is. Is all this true?

1. You have an instance of SQL Server on the laptop

2. You have a user (UserA) in a Windows group (GroupA)

3. The Windows Group (GroupA) has been added as a login to SQL Server as a Windows account

4. When UserA tries to log in to the local instance of SQL Server on the laptop, they can't get in

5. If you add UserA to the local Instance of SQL Server on the laptop, they can get in

Do I have all that right?

|||

All of that is correct.

Thanks,

-Dave

|||

Then let me ask a few more questions:

1. Is the user a domain Windows user?

2. Which service accounts did you use to start SQL Server on the laptop? Are they starting with a user account or the localsystem account?

|||

The user is a domain Windows user. Originally the laptop was using the local system account as its service account, but I thought that might be the problem so I tried using the same service account (a domain user account that I configured specifically as a SQL Server Service Account) that I use on the server, but unfortunately I got the same results.

Thanks,

-Dave

|||

OK - I think we're getting closer. I'm going to assume the laptop is on the domain, since you say you've added that Windows group to SQL Server as a login. Does the account that you are starting SQL Server with on the laptop have the ability to resolve network names?

To help investigate your problem, make sure you check the logs the system creates. You can read more about that here:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=242&rl=1

|||

The laptop is definitely on the domain. As far as the account that I am starting SQL Server with on the laptop having the ability to resolve network names, I'm not sure-how would I find that out?

Thanks,

Dave

|||I'm sure there's an easier way, but I normally just log on as that user, create a local group and then try to add someone from the domain to that local group.|||

Hi,

The SQL Server Service Account that I'm using does have the ability to resolve network names on the laptop.

Thanks,

-Dave

|||

Very odd. At this point, it sounds like you're doing everything properly. It has to be something to do with the security policies on that machine, but without access to it I'm afraid it would take a lot of back-and-forth to figure out where it is. I would suggest checking the logs for the system, using the techniques I outline in this guide:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=242&rl=1

If that doesn't help point out the issue, then you may have to open a support call so that someone can gain access to your machine. sorry that I couldn't be more help here.

|||

Dave, I don't know if this will help, but could you check the error message for the login failure in the ERRORLOG file and post here the two lines that describe the state and message of the error?

Thanks
Laurentiu

|||

Thanks for all the suggestions. It turns out that there was something going on with this group (I'm not sure what). I created another group in the same Active Directory location with the same users as members and it works fine.

Thanks,

-Dave

Cannot connect to localhost using Windows Security Group as login

I have a test environment set up at home with a small windows server 2003 Active Directory domain. I created a windows security group on the dc and added myself and another user to the group. I then use this group as a login for SQL Server and add the login to the sysadmin server role. This works exactly as I would expect on the Windows Server computer (which is also the Domain Controller) regardless of which computer I use to connect to this Windows Server computer. However, when I set up the exact same login on the local sql server instance of my client laptop running windows xp pro, the I get an error message (Login failed for user MYDOMAIN\dgolds...) when I attempt to connect to the local instance of SQL server, even though I am able to connect to the instance on the domain controller with no problems using this same client laptop under the same user account. The only way I am able to connect locally on the laptop under this account (which does not have local admin privileges on any box, for testing purposes) is to add the user account separately as a login, rather than as part of a group. I'm curious as to why being a member of a security group that is part of the sysadmin server role does not allow me to connect to the local instance of SQL Server on this client laptop. The windows server computer is running SQL2k5 Enterprise, and the client laptop is running SQL2k5 developer. Any insight into this would be appreciated.

Thanks,

Dave

Let's focus on the laptop for a moment, becuase that seems to be where your question is. Is all this true?

1. You have an instance of SQL Server on the laptop

2. You have a user (UserA) in a Windows group (GroupA)

3. The Windows Group (GroupA) has been added as a login to SQL Server as a Windows account

4. When UserA tries to log in to the local instance of SQL Server on the laptop, they can't get in

5. If you add UserA to the local Instance of SQL Server on the laptop, they can get in

Do I have all that right?

|||

All of that is correct.

Thanks,

-Dave

|||

Then let me ask a few more questions:

1. Is the user a domain Windows user?

2. Which service accounts did you use to start SQL Server on the laptop? Are they starting with a user account or the localsystem account?

|||

The user is a domain Windows user. Originally the laptop was using the local system account as its service account, but I thought that might be the problem so I tried using the same service account (a domain user account that I configured specifically as a SQL Server Service Account) that I use on the server, but unfortunately I got the same results.

Thanks,

-Dave

|||

OK - I think we're getting closer. I'm going to assume the laptop is on the domain, since you say you've added that Windows group to SQL Server as a login. Does the account that you are starting SQL Server with on the laptop have the ability to resolve network names?

To help investigate your problem, make sure you check the logs the system creates. You can read more about that here:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=242&rl=1

|||

The laptop is definitely on the domain. As far as the account that I am starting SQL Server with on the laptop having the ability to resolve network names, I'm not sure-how would I find that out?

Thanks,

Dave

|||I'm sure there's an easier way, but I normally just log on as that user, create a local group and then try to add someone from the domain to that local group.|||

Hi,

The SQL Server Service Account that I'm using does have the ability to resolve network names on the laptop.

Thanks,

-Dave

|||

Very odd. At this point, it sounds like you're doing everything properly. It has to be something to do with the security policies on that machine, but without access to it I'm afraid it would take a lot of back-and-forth to figure out where it is. I would suggest checking the logs for the system, using the techniques I outline in this guide:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=242&rl=1

If that doesn't help point out the issue, then you may have to open a support call so that someone can gain access to your machine. sorry that I couldn't be more help here.

|||

Dave, I don't know if this will help, but could you check the error message for the login failure in the ERRORLOG file and post here the two lines that describe the state and message of the error?

Thanks
Laurentiu

|||

Thanks for all the suggestions. It turns out that there was something going on with this group (I'm not sure what). I created another group in the same Active Directory location with the same users as members and it works fine.

Thanks,

-Dave

Cannot connect to localhost using Windows Security Group as login

I have a test environment set up at home with a small windows server 2003 Active Directory domain. I created a windows security group on the dc and added myself and another user to the group. I then use this group as a login for SQL Server and add the login to the sysadmin server role. This works exactly as I would expect on the Windows Server computer (which is also the Domain Controller) regardless of which computer I use to connect to this Windows Server computer. However, when I set up the exact same login on the local sql server instance of my client laptop running windows xp pro, the I get an error message (Login failed for user MYDOMAIN\dgolds...) when I attempt to connect to the local instance of SQL server, even though I am able to connect to the instance on the domain controller with no problems using this same client laptop under the same user account. The only way I am able to connect locally on the laptop under this account (which does not have local admin privileges on any box, for testing purposes) is to add the user account separately as a login, rather than as part of a group. I'm curious as to why being a member of a security group that is part of the sysadmin server role does not allow me to connect to the local instance of SQL Server on this client laptop. The windows server computer is running SQL2k5 Enterprise, and the client laptop is running SQL2k5 developer. Any insight into this would be appreciated.

Thanks,

Dave

Let's focus on the laptop for a moment, becuase that seems to be where your question is. Is all this true?

1. You have an instance of SQL Server on the laptop

2. You have a user (UserA) in a Windows group (GroupA)

3. The Windows Group (GroupA) has been added as a login to SQL Server as a Windows account

4. When UserA tries to log in to the local instance of SQL Server on the laptop, they can't get in

5. If you add UserA to the local Instance of SQL Server on the laptop, they can get in

Do I have all that right?

|||

All of that is correct.

Thanks,

-Dave

|||

Then let me ask a few more questions:

1. Is the user a domain Windows user?

2. Which service accounts did you use to start SQL Server on the laptop? Are they starting with a user account or the localsystem account?

|||

The user is a domain Windows user. Originally the laptop was using the local system account as its service account, but I thought that might be the problem so I tried using the same service account (a domain user account that I configured specifically as a SQL Server Service Account) that I use on the server, but unfortunately I got the same results.

Thanks,

-Dave

|||

OK - I think we're getting closer. I'm going to assume the laptop is on the domain, since you say you've added that Windows group to SQL Server as a login. Does the account that you are starting SQL Server with on the laptop have the ability to resolve network names?

To help investigate your problem, make sure you check the logs the system creates. You can read more about that here:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=242&rl=1

|||

The laptop is definitely on the domain. As far as the account that I am starting SQL Server with on the laptop having the ability to resolve network names, I'm not sure-how would I find that out?

Thanks,

Dave

|||I'm sure there's an easier way, but I normally just log on as that user, create a local group and then try to add someone from the domain to that local group.|||

Hi,

The SQL Server Service Account that I'm using does have the ability to resolve network names on the laptop.

Thanks,

-Dave

|||

Very odd. At this point, it sounds like you're doing everything properly. It has to be something to do with the security policies on that machine, but without access to it I'm afraid it would take a lot of back-and-forth to figure out where it is. I would suggest checking the logs for the system, using the techniques I outline in this guide:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=242&rl=1

If that doesn't help point out the issue, then you may have to open a support call so that someone can gain access to your machine. sorry that I couldn't be more help here.

|||

Dave, I don't know if this will help, but could you check the error message for the login failure in the ERRORLOG file and post here the two lines that describe the state and message of the error?

Thanks
Laurentiu

|||

Thanks for all the suggestions. It turns out that there was something going on with this group (I'm not sure what). I created another group in the same Active Directory location with the same users as members and it works fine.

Thanks,

-Dave

Cannot connect to localhost using Windows Security Group as login

I have a test environment set up at home with a small windows server 2003 Active Directory domain. I created a windows security group on the dc and added myself and another user to the group. I then use this group as a login for SQL Server and add the login to the sysadmin server role. This works exactly as I would expect on the Windows Server computer (which is also the Domain Controller) regardless of which computer I use to connect to this Windows Server computer. However, when I set up the exact same login on the local sql server instance of my client laptop running windows xp pro, the I get an error message (Login failed for user MYDOMAIN\dgolds...) when I attempt to connect to the local instance of SQL server, even though I am able to connect to the instance on the domain controller with no problems using this same client laptop under the same user account. The only way I am able to connect locally on the laptop under this account (which does not have local admin privileges on any box, for testing purposes) is to add the user account separately as a login, rather than as part of a group. I'm curious as to why being a member of a security group that is part of the sysadmin server role does not allow me to connect to the local instance of SQL Server on this client laptop. The windows server computer is running SQL2k5 Enterprise, and the client laptop is running SQL2k5 developer. Any insight into this would be appreciated.

Thanks,

Dave

Let's focus on the laptop for a moment, becuase that seems to be where your question is. Is all this true?

1. You have an instance of SQL Server on the laptop

2. You have a user (UserA) in a Windows group (GroupA)

3. The Windows Group (GroupA) has been added as a login to SQL Server as a Windows account

4. When UserA tries to log in to the local instance of SQL Server on the laptop, they can't get in

5. If you add UserA to the local Instance of SQL Server on the laptop, they can get in

Do I have all that right?

|||

All of that is correct.

Thanks,

-Dave

|||

Then let me ask a few more questions:

1. Is the user a domain Windows user?

2. Which service accounts did you use to start SQL Server on the laptop? Are they starting with a user account or the localsystem account?

|||

The user is a domain Windows user. Originally the laptop was using the local system account as its service account, but I thought that might be the problem so I tried using the same service account (a domain user account that I configured specifically as a SQL Server Service Account) that I use on the server, but unfortunately I got the same results.

Thanks,

-Dave

|||

OK - I think we're getting closer. I'm going to assume the laptop is on the domain, since you say you've added that Windows group to SQL Server as a login. Does the account that you are starting SQL Server with on the laptop have the ability to resolve network names?

To help investigate your problem, make sure you check the logs the system creates. You can read more about that here:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=242&rl=1

|||

The laptop is definitely on the domain. As far as the account that I am starting SQL Server with on the laptop having the ability to resolve network names, I'm not sure-how would I find that out?

Thanks,

Dave

|||I'm sure there's an easier way, but I normally just log on as that user, create a local group and then try to add someone from the domain to that local group.|||

Hi,

The SQL Server Service Account that I'm using does have the ability to resolve network names on the laptop.

Thanks,

-Dave

|||

Very odd. At this point, it sounds like you're doing everything properly. It has to be something to do with the security policies on that machine, but without access to it I'm afraid it would take a lot of back-and-forth to figure out where it is. I would suggest checking the logs for the system, using the techniques I outline in this guide:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=242&rl=1

If that doesn't help point out the issue, then you may have to open a support call so that someone can gain access to your machine. sorry that I couldn't be more help here.

|||

Dave, I don't know if this will help, but could you check the error message for the login failure in the ERRORLOG file and post here the two lines that describe the state and message of the error?

Thanks
Laurentiu

|||

Thanks for all the suggestions. It turns out that there was something going on with this group (I'm not sure what). I created another group in the same Active Directory location with the same users as members and it works fine.

Thanks,

-Dave

Cannot connect to database

I have SQL Server 2000 Developers Edition install at work and home
machines., I configure as Windows Authentication.
I am using the CASE Tools DBDesigner to connect to SQL Server.
However, I got probelm in connecting to SQL SErver 2000 at home, the
scenario is below:
1)I create a Windows user in Windows 2000 Professional by Control Panel
2)Go to SQL Server Enterprise Manager
3)Goto Security, create Logins
4)Goto Northwind database
5)Add Database User from Users folder
The problem is it works at work machine, but when I do the same at home
machine, I got different things.
At home machine, I got the new user has domain name prefix, it does not
appear in my work machine.
Now, at home machine, I tried to connect from DBDesigner 4, I got the error
something like the login is not associated with trusted login.
Why it was happened like this ?Alan
Try to switch Authentication to SQL Server and Windows.
Connect to SQL Server as 'sa'
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:e%23AlYYoTEHA.1548@.TK2MSFTNGP11.phx.gbl...
> I have SQL Server 2000 Developers Edition install at work and home
> machines., I configure as Windows Authentication.
> I am using the CASE Tools DBDesigner to connect to SQL Server.
> However, I got probelm in connecting to SQL SErver 2000 at home, the
> scenario is below:
> 1)I create a Windows user in Windows 2000 Professional by Control Panel
> 2)Go to SQL Server Enterprise Manager
> 3)Goto Security, create Logins
> 4)Goto Northwind database
> 5)Add Database User from Users folder
> The problem is it works at work machine, but when I do the same at home
> machine, I got different things.
> At home machine, I got the new user has domain name prefix, it does not
> appear in my work machine.
> Now, at home machine, I tried to connect from DBDesigner 4, I got the
error
> something like the login is not associated with trusted login.
> Why it was happened like this ?
>|||What is the exact error you are getting?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Sorry for late reply.
Now I need to list my details of my work machine SQL Server Developer
editions within Enterprise Manager:
- Use Windows authentication
- I add one login eg. 'bbb', password with 'bbb' from Security|Logins
I have NOT added user in Northwind database yet.
Now leave Enterprise Manager.
Goto DBDesigner 4, select the option reverse engineering, select the
connection to Northwind database, type in 'bbb' with password 'bbb'.
Magic is coming out: DBDesigner can reengineer the logical database
structure for me.
This is so far so good in my working place machine.
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:W4ql$9uTEHA.304@.cpmsftngxa10.phx.gbl...
> What is the exact error you are getting?
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

Cannot connect to (local)

SQL Server 2005 SP1 on my home PC has stopped responding to (local). I
can now only connect from my applications by using the machine's name,
even though SQL Management Studio is using (local). There's no network
or anything, just a standalone PC running XP. I can't put my finger on
anything I've installed or fiddled with that coincides with this change
of behaviour. Anyone got any ideas, please?
David
Have you looked at ERROR.LOG file? Any entries?
What is the error you are getting?
"David Wimbush" <david_wimbush@.hotmail.com> wrote in message
news:1168244667.602133.108490@.s34g2000cwa.googlegr oups.com...
> SQL Server 2005 SP1 on my home PC has stopped responding to (local). I
> can now only connect from my applications by using the machine's name,
> even though SQL Management Studio is using (local). There's no network
> or anything, just a standalone PC running XP. I can't put my finger on
> anything I've installed or fiddled with that coincides with this change
> of behaviour. Anyone got any ideas, please?
>
|||Good point, Uri, I missed that part. It just times out trying to
establish the connection.

Cannot connect to (local)

SQL Server 2005 SP1 on my home PC has stopped responding to (local). I
can now only connect from my applications by using the machine's name,
even though SQL Management Studio is using (local). There's no network
or anything, just a standalone PC running XP. I can't put my finger on
anything I've installed or fiddled with that coincides with this change
of behaviour. Anyone got any ideas, please?David
Have you looked at ERROR.LOG file? Any entries?
What is the error you are getting?
"David Wimbush" <david_wimbush@.hotmail.com> wrote in message
news:1168244667.602133.108490@.s34g2000cwa.googlegroups.com...
> SQL Server 2005 SP1 on my home PC has stopped responding to (local). I
> can now only connect from my applications by using the machine's name,
> even though SQL Management Studio is using (local). There's no network
> or anything, just a standalone PC running XP. I can't put my finger on
> anything I've installed or fiddled with that coincides with this change
> of behaviour. Anyone got any ideas, please?
>|||Good point, Uri, I missed that part. It just times out trying to
establish the connection.

Cannot connect to (local)

SQL Server 2005 SP1 on my home PC has stopped responding to (local). I
can now only connect from my applications by using the machine's name,
even though SQL Management Studio is using (local). There's no network
or anything, just a standalone PC running XP. I can't put my finger on
anything I've installed or fiddled with that coincides with this change
of behaviour. Anyone got any ideas, please?David
Have you looked at ERROR.LOG file? Any entries?
What is the error you are getting?
"David Wimbush" <david_wimbush@.hotmail.com> wrote in message
news:1168244667.602133.108490@.s34g2000cwa.googlegroups.com...
> SQL Server 2005 SP1 on my home PC has stopped responding to (local). I
> can now only connect from my applications by using the machine's name,
> even though SQL Management Studio is using (local). There's no network
> or anything, just a standalone PC running XP. I can't put my finger on
> anything I've installed or fiddled with that coincides with this change
> of behaviour. Anyone got any ideas, please?
>|||Good point, Uri, I missed that part. It just times out trying to
establish the connection.