mixed mode in MS SQL

[judithSampathwa]

hi,
i have a problem in login in to the MS SQL server 2008,
i installed the MS SQL with the authentivcation mode as Mixed mode and and for the SQL server administrator i added the current user and for the password i gave a different password, which is not the same as for when looing in to the windows operating system,

and when i start MS SQL server and then i change the mode to mixed mode and then i restart the server,
then when i try to login as SQL server authentication the user name is the name that i selected in the installation process of the authentication mode, and what is the password that i should use,
is it the one i specified in the installation process of the one used for loogin in to windows,

can please someone help me with this,
thanx

and also i have another question, i have a desktop application which is written in visual studio 2008 C#, and i copied that project to a shared folder in the network and when i try to connect tto the server do i have to give the username and the password of the windoes login which the MS SQL server is????

can some one help me in this to situation or please give me some guidelines

[buddylee17]

In SQL Server, you have 2 login options:

1.) Windows Authentication: No password allowed. The credentials used when logging in to the machine are pushed forward and verified by either the local machine or the Active Directory (if on a network) and then compared with credentials in the database to verify the principals permissions.

2.) SQL Server Authentication: Password is required and user has absolutely no relationship to any local or network users. User is authenticated by SQL Server.

You need to understand that mixed mode does not mean mixing users up. It means either use a trusted connection (Windows Authentication) or require that the user logs in with a valid username / password combination (stored in SQL Server).

sa is a good example of a SQL Server Login. It has absolutely no relationship to any user on the network, but has a password stored in SQL Server and has unlimited permissions in the database.

[judithSampathwa]

In SQL Server, you have 2 login options:

1.) Windows Authentication: No password allowed. The credentials used when logging in to the machine are pushed forward and verified by either the local machine or the Active Directory (if on a network) and then compared with credentials in the database to verify the principals permissions.

2.) SQL Server Authentication: Password is required and user has absolutely no relationship to any local or network users. User is authenticated by SQL Server.

You need to understand that mixed mode does not mean mixing users up. It means either use a trusted connection (Windows Authentication) or require that the user logs in with a valid username / password combination (stored in SQL Server).

sa is a good example of a SQL Server Login. It has absolutely no relationship to any user on the network, but has a password stored in SQL Server and has unlimited permissions in the database.

yeah i know the difference between those authentication types,
but how can i log in to the server from a remote computer to the sql server
1.in windows authentication
in visual studio i add the the server to the server explorer and then get the connection string and added in to my DB file, i can connect to the server, compiling the application in the localhost itself. but when i run the project in a remote computer and runs the desktop application, it gives an error saying that "Login failed for user 'sa'". can't i connect from a remote computer to the server computer MS SQL in windows authentication??? do i have to include the windows login username and te password in the connection string for this

2.mixed mode authentication
when i copile the application in the localhost where the server is installed and try to login in mixed mode authentication, i have the MS SQL mixed mode authentication username and the password in the connection string as well, but the same error occurs, "Login failed for user 'sa'"
what is the problem in this

please can youhelp me in this

apprieciate a lot thanxxxx

[buddylee17]

From within the network (and assuming that your app runs under a user with privileges to the database), use windows authentication. Notice there is no username, as the app runs under a user context (maybe NT AUTHORITY\NETWORK SERVICE).

Data Source=YOUR SERVER NAME;Initial Catalog=YOUR DATABASE NAME;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;

Alternatively, use SQL Server Authentication:

Data Source=YOUR SERVER NAME; Initial Catalog=YOUR DATABASE NAME; Provider=SQLNCLI10.1;User ID=YOUR USERNAME;Password=YOUR PASSWORD;Auto Translate=False;

See the connection strings website for all the different connection strings which can be used.

[judithSampathwa]

From within the network (and assuming that your app runs under a user with privileges to the database), use windows authentication. Notice there is no username, as the app runs under a user context (maybe NT AUTHORITY\NETWORK SERVICE).

Data Source=YOUR SERVER NAME;Initial Catalog=YOUR DATABASE NAME;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;

Alternatively, use SQL Server Authentication:

Data Source=YOUR SERVER NAME; Initial Catalog=YOUR DATABASE NAME; Provider=SQLNCLI10.1;User ID=YOUR USERNAME;Password=YOUR PASSWORD;Auto Translate=False;

See the connection strings website for all the different connection strings which can be used.

hey yes,
i added the same as you have shown,

but in mixed mode authentication, i have the username sa. in the same machine i have installed the ms sql i run the desktop application. but the windows login does not have admin previlages.
the test connection succedds but when i try to connect to the MS SQl by running the application, i get an error saying "login falied for user 'sa'"

and it gives a "ExecuteReader requires an open and available Connection. The connection's current state is closed." after the error msg login failed for use sa msg..

do u know what is the problem with this????

the connection string i am using is as below.
private static String constring = @"Data Source=DESKTOP-066;Initial Catalog=AdminApplicationDB;User ID=sa;Password=***********";

but when i put auto translate false , there is an The type initializer for 'Admin_Application.DataAccessLayer.DB' threw an exception.

Type initalization exception

hey please can you help with this

thanxxxxxxxx a lot

[buddylee17]

Sounds like Sql Server is not configured to allow remote connections.

Can you open Sql Server Management Studio and connect to the server? If so, right click the instance in question, go to Properties -> Connections -> Check "Allow remote connections to this server".

May also want to verify TCP/IP is enabled on the server via Sql Server Configuration Manager.

[judithSampathwa]

Sounds like Sql Server is not configured to allow remote connections.

Can you open Sql Server Management Studio and connect to the server? If so, right click the instance in question, go to Properties -> Connections -> Check "Allow remote connections to this server".

May also want to verify TCP/IP is enabled on the server via Sql Server Configuration Manager.

hey ,
Allow remote connections to this server the check boxed was checked,

tcp ip is enabled only,
what would be the problem be

[buddylee17]

Go into services and verify that SQL Server Browser is running. If it's not, start it.

[judithSampathwa]

Go into services and verify that SQL Server Browser is running. If it's not, start it.

the sql server browser is runing..........

i have no idea why is this happening,
there is three servers running in the sql server configuration manager
MSSMLBIZ,SQLEXPRESS,and MSSQLSERVER which is the one i can't connect to the server in mixed mode authentication.