Hi guys,
got another simple question (I am very new to sqlserver...), I have a c# desktop program, it connected to a sqlexpress 2005 server, and I need my program to connect to sql server reomotely , so I turn on TCP/IP protocol, my details are in below:
inside the server's tcp/ip on the ip tab, IP1 is set to active, enabled, the ip address is 192.168.0.3 , there is no port number for IP1, should there be a port number? dynamic port is 0
IP2 is set to active, enable with address 127.0.0.1 and also does not have a port number, dynamic port is 0
IPAll is using dynamic port 1921 but also has no port number.
for the client TCP/IP setting the default port is 1433 and it is also enabled.
I then set up a port forwarding service in my router with port (1921, I think that's the port number ?)when I run my application and sqlexpress server on the same machine, both of the following connectionstrings are working well for the program:
server= 62.31.81.210.\SQLEXPRESS,1921; user id='sa'; password='mypassword'; Database='EvoHealthSQLex'; Integrated Security=True
server= 192.168.0.3.\SQLEXPRESS,1921; user id='sa'; password='mypassword'; Database='EvoHealthSQLex'; Integrated Security=True
(I use dynamic port 1921 as the port number, don't know if it's right, but it works, but what do we need for TCP/IP setting the default port 1433? )
62.31.81.210 is the router's IP address, I attach sqlexpress's machine's(local ip address 192.168.0.3) on port forwarding service with port 1921, so above connection string is working right as I want to test wheter I can connect to sqlexpress server via real Ip address not just local Ip address.
then there is the problem: I try to run the same program on the other computer, but within the same local network (both sqlexpress server machine and application machine are connected to the same router ), I got a error message:
Login failed for user 'BRISTOL-1\Guest'
after some researching, I did the following:
I open the sql server management studio express(also free), go to 'Database' ->'EvoHealthSQLex'-> 'properties' ->'permissions'
I add a user called 'BRISTOL-1\Guest' and granted it with 'connect' and 'control' permissions, however, I still got the same error.
In 'Database' ->'EvoHealthSQLex'-> 'properties' ->'general'
I saw the owner part said 'BRISTOL-1\Ray'
the full computer name of machine running sqlexpress is 'bristol-1', and I login as user 'ray', I guess that's why the database owner is 'BRISTOL-1\Ray', on the other computer (which run the application), the full computer name is 'ray' and I login as 'rui', I don't know whether those info is useful.
Questions:
I wonder why the same error occured after I created the user 'BRISTOL-1\Guest', and I don't know why I am recoginzed as 'BRISTOL-1\Guest' on the other machine?
I wonder if I created a user, for example,say 'BRISTOL-1\ross' (does it have to use the same prefix BRISTOL-1?), when I use the program in other computer how do I specified myself as user 'BRISTOL-1\ross' or whatever ? does it have anything to do with connectionstring? as in the connection string, user id is 'sa', i don't think I should change the connectionstring to:
server= 192.168.0.3.\SQLEXPRESS,1921; user id='BRISTOL-1\ross' ; password='mypassword'; Database='EvoHealthSQLex'; Integrated Security=True - if this case is right, what the pw suppose to be?
I know those suppose to be very simple for you experts, I am just very new to sqlserver, and I can't find any related articles online...(is it because the solution is too obvious?)
I don't have anyone around me can help me so I post it here, any help will be very appreciate!!!
Ps: I just turn on the 'Guest' account in 'User Account' of 'Control Panel', but same error.
"user id" and "password" is used for SQL Authentication, while Integrated Security" is used for windows authentication. You needs to only use one of them. If both is specified, the result is not determinstics. I know some driver ignores SQL authentication in this case.
How to use configure these two authentication is a long story, I am sure you can find plenty of articles on the web or use bookonline.
In you case, if you use sa password, make sure you don't specify Integrated Security. If you want to use windows authentication, make sure that you client account has access privilieges to the SQL Server account.
|||Hi, thanks so much for your rapid reply, I realized that I asked many stupid questions...
I just make a small progress now, after I downloaded BOL (just now), I fint out I need to greate a login rather than just create a user! :
the whole command:
CREATE LOGIN login_name { WITH <option_list1> | FROM <sources> } <sources> ::= WINDOWS [ WITH <windows_options> [ ,... ] ] | CERTIFICATE certname | ASYMMETRIC KEY asym_key_name <option_list1> ::= PASSWORD = 'password' [ HASHED ] [ MUST_CHANGE ] [ , <option_list2> [ ,... ] ] <option_list2> ::= SID = sid | DEFAULT_DATABASE = database | DEFAULT_LANGUAGE = language | CHECK_EXPIRATION = { ON | OFF} | CHECK_POLICY = { ON | OFF} | CREDENTIAL = credential_name <windows_options> ::= DEFAULT_DATABASE = database | DEFAULT_LANGUAGE = languageIn my case I did:CREATE LOGIN [BRISTOL-1\Guest] FROM WINDOWS; GOthen it works, I wonder is it anyother computer try to connect with the same sqlexpress server will be BRISTOL-1\Guest ?now I need to figure out how to connect from outside rather than local network, any suggestions :)thanks a lot Nan Tu!
Caddre wrote: