none
installation of SQL server

    Question

  • Hi Guys,

    I have installed SQL Server 2008 to one of my PC. I will use this as our server for SQL training Lab. I've use dafault instance for my instance configuration which its appear MSSQLSERVER as instance ID. Then for the server configuration I used the same account for all SQL server services which the account name and password is came from the username and password of the workstation (pc). i have use windows authentication for database engine configuration. btw, what is the use of specify SQL server administator in the mixed mode?

    after installation i check the status (under services) of SQL Server(MSSQLSERVER) which  is started, includes SQL sever agent, SQL server browser  which the status is started.

    My Question are the following:

    1. When logging in the SQL,  the username accepted is (local) not (MSSQLSERVER) which is my default instance. it display in Object Explorer like this (local)(SQL Server XX.XX.XXX-PHD12345\lab.test). anyway. i already created a database name and its called TestDB.

    2. I have a desktop  with installed SQL 2008 as client.  i want this to connect or access the server that i created. my problem is Why i cannot connect or access that server? Any idea guys. thanks.

       I tried this but got an error:

         Server name: PHD12345\lab.test

         Authentication : Windows Authentication

    Wednesday, October 02, 2013 5:55 AM

Answers

  • Its already working. i just reinstall the whole SQL and then configure the client user to access from server.

    Another issue that i encounter when i populate the second table i got an error.

    here is what i did populating data. i have a CSV file then extract the records to temp table using bulk insert then insert it to the table created.

    here is the error i got.

    i have 2 primary key itemid and dataareaid.

    I already remove the duplicate records from itemid but still got this error.

    Msg 2627, Level 14, State 1, Line 1
    Violation of PRIMARY KEY constraint 'PK__Inventta__EAE40AAB0F975522'. Cannot insert duplicate key in object 'dbo.mytable'.
    The statement has been terminated.

    Friday, October 04, 2013 12:55 AM

All replies


  • My Question are the following:

    1. When logging in the SQL,  the username accepted is (local) not (MSSQLSERVER) which is my default instance. it display in Object Explorer like this (local)(SQL Server XX.XX.XXX-PHD12345\lab.test). anyway. i already created a database name and its called TestDB.

    2. I have a desktop  with installed SQL 2008 as client.  i want this to connect or access the server that i created. my problem is Why i cannot connect or access that server? Any idea guys. thanks.

       I tried this but got an error:

         Server name: PHD12345\lab.test

         Authentication : Windows Authentication

    1. If you have just one instance of SQL server the default instance instead of giving name (MSSQLSERVER) you can give local or dot(.) to connect to default instance ,this feature is provided by MS.You can type MSSQLSERVER and can also connect.Both ways will work

    2. What is the error which you got ? Enable mixed mode authentication on Server and then try to connect using Client.Make sure port1433(default ) is opened for your client machine  .Make sure SQL server browser service is running on SQL server which you want to connect.

    HTH


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621 Wednesday, October 02, 2013 6:35 AM
    Wednesday, October 02, 2013 6:05 AM
  • Hi,

    For #2.  SQL Browser is running.  Under protocol name VIA, status Disabled.  under Properties this are the info. default port is 0:1433, enabled is no, then listen info 0:1433.

    Wednesday, October 02, 2013 9:56 AM
  • Hi,

    For #2.  SQL Browser is running.  Under protocol name VIA, status Disabled.  under Properties this are the info. default port is 0:1433, enabled is no, then listen info 0:1433.


    Please enable TCP/IP protocol and try connecting.Can you please post complete error when you are trying to connect to server you are not replying completely to my questions.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, October 02, 2013 9:59 AM
  • Btw, where should i check the default 1433? should be  in SQL native client configuration, under client protocols?
    Wednesday, October 02, 2013 10:00 AM
  • its already enabled the TCP/IP and also check the all remote connection to this server. then i tried to ping the server (PC) and it was succesful.

    Here is the error i got when trying to connect the client to sql server.

    Client computer:

    servertype: Database Engine

    servername: PHD12345\MSSQLSERVER     or PH12345\lab.test

    authentication:Windows authentication

    Cannot connect to PHD12345\MSSQLSERVER.

    ===================================

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid) (.Net SqlClient Data Provider)


    • Edited by Lenoj Thursday, October 03, 2013 12:59 AM add
    Thursday, October 03, 2013 12:36 AM
  • its already enabled the TCP/IP and also check the all remote connection to this server. then i tried to ping the server (PC) and it was succesful.

    Here is the error i got when trying to connect the client to sql server.

    Client computer:

    servertype: Database Engine

    servername: PHD12345\MSSQLSERVER     or PH12345\lab.test

    authentication:Windows authentication

    Cannot connect to PHD12345\MSSQLSERVER.

    ===================================

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid) (.Net SqlClient Data Provider)


    Hello ,

    Since your instance is default instance don't provide name like PHD12345\MSSQLSERVER

    Just in connection string instead of above provide PHD12345.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, October 03, 2013 5:01 AM
  • A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid) (.Net SqlClient Data Provider)

    >>It says clearly that connection is not able establish as Shanky said you try like that.

    Also please verify this as well in the connection string PHD12345,<portnumber> if by default it didn't work PHD12345(usually it will work).

    >>Also verify you defined the right security authentication in the string & database name entered is correct one.

    see for other info-

    http://blogs.technet.com/b/mdegre/archive/2011/06/04/resolve-sql-server-connectivity-issues.aspx


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Thursday, October 03, 2013 8:53 AM
  • Its already working. i just reinstall the whole SQL and then configure the client user to access from server.

    Another issue that i encounter when i populate the second table i got an error.

    here is what i did populating data. i have a CSV file then extract the records to temp table using bulk insert then insert it to the table created.

    here is the error i got.

    i have 2 primary key itemid and dataareaid.

    I already remove the duplicate records from itemid but still got this error.

    Msg 2627, Level 14, State 1, Line 1
    Violation of PRIMARY KEY constraint 'PK__Inventta__EAE40AAB0F975522'. Cannot insert duplicate key in object 'dbo.mytable'.
    The statement has been terminated.

    Friday, October 04, 2013 12:55 AM
  • Its already working. i just reinstall the whole SQL and then configure the client user to access from server.

    Another issue that i encounter when i populate the second table i got an error.

    here is what i did populating data. i have a CSV file then extract the records to temp table using bulk insert then insert it to the table created.

    here is the error i got.

    i have 2 primary key itemid and dataareaid.

    I already remove the duplicate records from itemid but still got this error.

    Msg 2627, Level 14, State 1, Line 1
    Violation of PRIMARY KEY constraint 'PK__Inventta__EAE40AAB0F975522'. Cannot insert duplicate key in object 'dbo.mytable'.
    The statement has been terminated.

    Hi Lenoj,

    According to your description, your questions about username and remotely connect to SQL Server from client have resolved by yourself. Thanks for your share your solution and mark it as answer. About another issue of violation in PRIMARY KEY constraint, you can recreate a new thread. It is appropriate and more experts will assist you.

    For more information about the similar error issue, you can review the following link.
    http://social.msdn.microsoft.com/Forums/en-US/8b539204-4258-4104-a4bd-fc94cd30d439/violation-of-primary-key-constraint-pkinfosysscan4c0144e4-cannot-insert-duplicate-key-in?forum=sqlgetstarted

    Thanks,
    Sofiya Li



    Sofiya Li
    TechNet Community Support

    Friday, October 04, 2013 2:41 AM
    Moderator