I setup SQl Server 2005 on my PC under my Windows account of @Domain\Madog. I have administrative privelages on my PC.
I can connect to SQL Server Management Studio using Windows Authenitication, and once logged in can see the above account @Domain\Madog
as both a valid server login account and also a user in several databases. For example, under the master database, @Domain\Madog
is listed as a user with the role of RSExecRole.
I cannot, however, connect to SQL Server using SQL Server Authentication and entering @Domain\Madog and password as credentials. I thought I would be able to do this, given I am listed as a server Login and a User in several databases.
The message I am receiving is:
"A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)"
So this is more about understanding than a particular problem.
check whether sql server authentication is enabled on your system or not.
go to management studio -> select the instance name and right click it -> properites -> in that window select security tab -> check in server authentication,
the sql server and windows authentication mode is selected or not. if not select it.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
I have checked this and 'SQL Server and Windows' mode is ticked.
I remember selecting mixed mode on setup. I can also connect to SQL Management Studio and connect from VS Studio using sa as the login.
are you have permissions for that user on the database? if not give the permissions.
security ->logins ->select that login->right click and select properties ->Now in UserMappings check whether the user have permissions for that database. give the db_owner for that database.
and also check the eventviewer for the error message it is giving.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
I have checked the security as you have suggested.
The default database is 'Master' and the login has the role of 'public' for this database.
I notice that the login 'sa' has db_owner access for 'master', but am unsure of the security aspects of allowing the domain user this role?
How do I check the Eventviewer - I am not sure where that is - unless you mean the Windows event log.
Madog
Member
65 Points
135 Posts
Using Windows Account as SQL Server Account
Oct 31, 2008 02:34 AM|LINK
I setup SQl Server 2005 on my PC under my Windows account of @Domain\Madog. I have administrative privelages on my PC.
I can connect to SQL Server Management Studio using Windows Authenitication, and once logged in can see the above account @Domain\Madog
as both a valid server login account and also a user in several databases. For example, under the master database, @Domain\Madog
is listed as a user with the role of RSExecRole.
I cannot, however, connect to SQL Server using SQL Server Authentication and entering @Domain\Madog and password as credentials. I thought I would be able to do this, given I am listed as a server Login and a User in several databases.
The message I am receiving is:
"A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)"
So this is more about understanding than a particular problem.
ramireddyind...
All-Star
31358 Points
4579 Posts
Re: Using Windows Account as SQL Server Account
Oct 31, 2008 04:58 AM|LINK
check whether sql server authentication is enabled on your system or not.
go to management studio -> select the instance name and right click it -> properites -> in that window select security tab -> check in server authentication,
the sql server and windows authentication mode is selected or not. if not select it.
Madog
Member
65 Points
135 Posts
Re: Using Windows Account as SQL Server Account
Nov 02, 2008 09:22 PM|LINK
I have checked this and 'SQL Server and Windows' mode is ticked.
I remember selecting mixed mode on setup. I can also connect to SQL Management Studio and connect from VS Studio using sa as the login.
Thanks
ramireddyind...
All-Star
31358 Points
4579 Posts
Re: Using Windows Account as SQL Server Account
Nov 03, 2008 02:35 AM|LINK
are you have permissions for that user on the database? if not give the permissions.
security ->logins ->select that login->right click and select properties ->Now in UserMappings check whether the user have permissions for that database. give the db_owner for that database.
and also check the eventviewer for the error message it is giving.
Madog
Member
65 Points
135 Posts
Re: Using Windows Account as SQL Server Account
Nov 06, 2008 08:48 PM|LINK
Thanks for sticking with this.
I have checked the security as you have suggested.
The default database is 'Master' and the login has the role of 'public' for this database.
I notice that the login 'sa' has db_owner access for 'master', but am unsure of the security aspects of allowing the domain user this role?
How do I check the Eventviewer - I am not sure where that is - unless you mean the Windows event log.