Sunday, March 11, 2012

Cannot login SQL Server with 'sa'

Hi,
After I setup 'sa' password in SQL Server 2005, I get out SQL Server and
re-login using SQL Authentication mode with 'sa' and pasword, I get error:
"A connection was successfully established with the server, but then the
error occured during the login process. (Provider: Shared memeoryprovider,
error 0 - No process is on the other end of the pipe.) (Microsoft SQL Server
: error 233)"
Please help
Thanks
kaiDo you have Shared Memory connections enabled? Run the SQL Server
Configuration Manager and look at the protocols and shared Memory to see if
it is enabled or not.
Andrew J. Kelly SQL MVP
"kai" <kailiang@.earthlink.net> wrote in message
news:5HZqf.9593$nm.595@.newsread2.news.atl.earthlink.net...
> Hi,
> After I setup 'sa' password in SQL Server 2005, I get out SQL Server and
> re-login using SQL Authentication mode with 'sa' and pasword, I get error:
> "A connection was successfully established with the server, but then the
> error occured during the login process. (Provider: Shared memeoryprovider,
> error 0 - No process is on the other end of the pipe.) (Microsoft SQL
> Server : error 233)"
> Please help
> Thanks
> kai
>|||Andrew,
Thanks for your help. I checked it, it is enabled. I found additional
error when I login second time:
"Login failed for user 'sa'.The user is associated with a trusted SQL Server
connection. (Microsoft SQL Server, error: 18452)
Kai
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23pGIiaACGHA.736@.TK2MSFTNGP10.phx.gbl...
> Do you have Shared Memory connections enabled? Run the SQL Server
> Configuration Manager and look at the protocols and shared Memory to see
> if it is enabled or not.
> --
> Andrew J. Kelly SQL MVP
>
> "kai" <kailiang@.earthlink.net> wrote in message
> news:5HZqf.9593$nm.595@.newsread2.news.atl.earthlink.net...
>|||See if this may help:
http://support.microsoft.com/?id=328306
Andrew J. Kelly SQL MVP
"kai" <kailiang@.earthlink.net> wrote in message
news:jB_qf.9068$3Z.3336@.newsread1.news.atl.earthlink.net...
> Andrew,
> Thanks for your help. I checked it, it is enabled. I found additional
> error when I login second time:
> "Login failed for user 'sa'.The user is associated with a trusted SQL
> Server connection. (Microsoft SQL Server, error: 18452)
> Kai
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23pGIiaACGHA.736@.TK2MSFTNGP10.phx.gbl...
>|||Is it possible you're trying to connect multiple times over the dedicated
Admin connection?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"kai" <kailiang@.earthlink.net> wrote in message
news:jB_qf.9068$3Z.3336@.newsread1.news.atl.earthlink.net...
> Andrew,
> Thanks for your help. I checked it, it is enabled. I found additional
> error when I login second time:
> "Login failed for user 'sa'.The user is associated with a trusted SQL
> Server connection. (Microsoft SQL Server, error: 18452)
> Kai
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23pGIiaACGHA.736@.TK2MSFTNGP10.phx.gbl...
>
>|||Hi Kai,
Generally speaking, error code 18452 suggests SQL Authentication is not
enabled. Would you please check to see whether it was enabled?
- Right Click the instance name in the Object Explorer, select Properties
- Select Security page and make sure SQL Server and Windows Authentication
mode is selected.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Michael ,
Thank you for your help.
It is enabled. I check my other two SQL Servers, they all have the same
problems.
Kai
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:8qxTKFFCGHA.3992@.TK2MSFTNGXA02.phx.gbl...
> Hi Kai,
> Generally speaking, error code 18452 suggests SQL Authentication is not
> enabled. Would you please check to see whether it was enabled?
> - Right Click the instance name in the Object Explorer, select Properties
> - Select Security page and make sure SQL Server and Windows Authentication
> mode is selected.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Michael,
Thank you very much for your help.
When I installed SQL Server 2005 initially, I use Wondows Authentication
mode. After the installation, I changed to Mixed mode. Then I setup the new
password for 'sa' and other SQL Server logins, I cannot login with any SQL
logins.
I just found out the Login properties (status) for those SQL logins ware
setup to "Disable" by default, after I change them to "Enaled", I can login
with SQL logins.
Thank you so much !!!
Kai
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:g8q%235hfCGHA.3992@.TK2MSFTNGXA02.phx.gbl...
> Hi Kai,
> This issue occurs because SQL Server Management Studio cannot correctly
> report the error when the maximum number of concurrent connections has
> been
> reached.
> To work around this issue, you can use the sp_configure Transact-SQL
> procedure to increase the maximum number of concurrent connections. You
> can
> also manage the instance of SQL Server 2005 in other ways. For example,
> you
> can use the sqlcmd utility.
> To use sp_configure to increase the maximum number of concurrent
> connections, follow these steps:
> 1. Click Start, click Run, type cmd, and then click OK.
> 2. At the command prompt, type the following command, and then press
> ENTER.
> sqlcmd.exe -E -S servername
> 3. When you successfully connect to the instance of SQL Server 2005,
> execute the following Transact-SQL statements at the SQL Server prompt.
> sp_configure 'user connections', <NewNumber>
> Go
> Reconfigure
> Go
> 4. Restart the SQL Server service for the change to take effect.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Kai,
Happy New Year! You are welcome!
If you have any questions or concerns next time, don't hesitate to let me
know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||By default max no. of connections is set to 10.
To increase the maximum no. of connections
1. In Object Explorer, right-click a server and click Properties.
2. Click the Connections node.
3. Under Connections, in the Max number of concurrent connections box, type
or select a value from 0 through 32767 to set the maximum number of users
that are allowed to connect simultaneously to the instance of SQL Server.
set it to 0 (it means unlimited connections)

No comments:

Post a Comment