locked
Sharing SQL databases between an xp laptop and W7 laptop RRS feed

  • Question

  • I am looking forsomeone with patience to step me through sharing an mdf file on a Windows 7 laptop withe a vb 2010 Express application on an xp laptop.  I am using sql 2008 R2 express on the W7 laptop and have enabled TCP/IP and SQL Browser.  I have allowed SQL Server Port and SQL Browser through the firewall on W7.  I have given a Guest login a server role of sysadmin. My W7 laptop name is "BandMJ-PC" and both laptops are connected via ethernet on a workgroup called BAAWGROUP.  I am able to file share between the laptops.  I feel my problem stems from the connection string which is:

    ""Data Source=BandMJ-PC\SQLEXPRESS;Initial Catalog=" & FileName & ";uid = Guest; User Instance=False"

    Where FileName is the path and pfile picked up from the vb OpenFileDialog on the application running on the xp laptop.

    I get the "Logon failed for user Guest" message.  I have spent the past week googling and trying everything I could find but to no avail.  Any help would be greatly appreciated.

    Brad


    BHend

    Friday, August 31, 2012 5:24 AM

Answers

  • Hi

    Please use the following Connection string

    Data Source=BandMJ\SQLEXPRESS,1433;Initial Catalog = MyDatabase.mdf;Integrated Security=False;User ID = "AucSlave"; Password="Password"

    Here 1433 is default TCP Port no for SQL Server

    Still not getting, Please check the following

    Open SQL server Management Studio ,
    1) Object Explorer -> Expand "Security" -> Right Click to create new User at SQL server and set all privileges for this user. (Right click on new user, select properties and we can get many options)

    2)Right click on Server Instance->
                             A) Properties ->Security -> Select "SQL server and Windows Authentication Mode"
                             B) Properties ->Connections ->Select "Allow Remote connections to this server"
                             C) Properties ->Permissions-> Select created user and Grant Permissions
    3)Start -> All Programs -> Sql Server 2008 R2 ->Configuration Tools -> SQL Server Configuration Manager

                             A)Expand each option on left side, and on clicking protocols , we can see Shared Memory, Named Pipes, TCP/IP and Via. Enable first 3 by right clicking on that
                             B)On Selecting SQL SERVER 2008 SERVICES on left panel, we can see "SQL SERVER" and "SQL SERVER BROWSER". Right click on each one and select properties. and do the following
                                    i) select tab "SERVICE", set "Start Mode" as automatic, Click Apply                                                              

      ii) select tab "LOG ON",  select "Built-inAccount:" , then select "Network Service" from Combo box, click apply. System will ask whether to restart service and restart.
      iii) We can restart the service by, Right click on SQL SERVER" and "SQL SERVER BROWSER and click "Restart"
      iv) Above steps has to be done for both SQL SERVER" and "SQL SERVER BROWSER

      v)select your instance from  'SQL Server Network Configuration'-----,  open properties of TCP/IP -----, In 'IP Adresses' Tab we can see "IP ALL' .  Set TCP Port value to 49477

    • Restart SQL Server and try

    4)Control panel-> Windows firewall->Advanced Setting->select Inbound Rules-> New Rule
        Create new rule for program "sqlbrowser.exe" (find the location of this files at SQL SERVER installalation folder)
        Create new rule for program "sqlserver.exe"
        Create new rule for port 1433

    Thanks

    Johnson

    • Marked as answer by BHend Sunday, October 28, 2012 10:54 PM
    Sunday, October 28, 2012 4:52 AM

All replies

  • please run xp_readerrorlog in the Management studio and paste the result

    Ramesh Babu Vavilla MCTS,MSBI

    Friday, August 31, 2012 5:38 AM
  • ""Data Source=BandMJ-PC\SQLEXPRESS;Initial Catalog=" & FileName & ";uid = Guest; User Instance=False"

    Where FileName is the path and pfile picked up from the vb OpenFileDialog on the application running on the xp laptop.

    Hello,

    This don't work; Initial Catalog is the parameter for the database name, you can't use a file name here.

    You only could open the database file as a "user instance" together with the AttachDBFilename + "User Instance=TRUE" parameter ; see SQL Server Express User Instances


    Olaf Helper
    Blog Xing

    Friday, August 31, 2012 5:48 AM
  • Hello Ramesh,

    I couldn't find a xp_readerrorlog within Sql Server Management Studio, however I did find the SQL Server Logs that showed an error while trying to connect.  These are the most recent entries:

    Date,Source,Severity,Message
    08/31/2012 16:18:21,spid51,Unknown,Using 'xpstar.dll' version '2009.100.1600' to execute extended stored procedure 'xp_enumerrorlogs'. This is an informational message only; no user action is required.
    08/31/2012 16:18:21,spid51,Unknown,Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
    08/31/2012 16:17:24,Logon,Unknown,Login failed for user 'Guest'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: 10.0.0.40]
    08/31/2012 16:17:24,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 58.
    08/31/2012 16:16:44,spid7s,Unknown,Recovery is complete. This is an informational message only. No user action is required.
    08/31/2012 16:16:44,spid13s,Unknown,Service Broker manager has started.
    08/31/2012 16:16:44,spid13s,Unknown,The Database Mirroring protocol transport is disabled or not configured.
    08/31/2012 16:16:44,spid13s,Unknown,The Service Broker protocol transport is disabled or not configured.
    08/31/2012 16:16:44,spid10s,Unknown,Starting up database 'tempdb'.
    08/31/2012 16:16:43,spid10s,Unknown,Clearing tempdb database.
    08/31/2012 16:16:43,Server,Unknown,SQL Server is now ready for client connections. This is an informational message; no user action is required.
    08/31/2012 16:16:43,Server,Unknown,The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b<c/> state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
    08/31/2012 16:16:43,Server,Unknown,Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection<c/> restart SQL Server using the trace flag 7806. This is an informational message only. No user action is required.
    08/31/2012 16:16:43,Server,Unknown,Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].
    08/31/2012 16:16:43,Server,Unknown,Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLEXPRESS ].


    BHend

    Friday, August 31, 2012 6:31 AM
  • Sorry, I realized what you meant and have run xp_readerrorlog with these results:

    LogDate ProcessInfo Text
    2012-08-31 16:16:42.490 Server Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)    Apr 22 2011 11:57:00    Copyright (c) Microsoft Corporation   Express Edition with Advanced Services on Windows NT 6.1 <X86> (Build 7601: Service Pack 1) 
    2012-08-31 16:16:42.490 Server (c) Microsoft Corporation.
    2012-08-31 16:16:42.490 Server All rights reserved.
    2012-08-31 16:16:42.490 Server Server process ID is 3784.
    2012-08-31 16:16:42.490 Server System Manufacturer: 'TOSHIBA', System Model: 'Satellite L650'.
    2012-08-31 16:16:42.490 Server Authentication mode is WINDOWS-ONLY.
    2012-08-31 16:16:42.490 Server Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\ERRORLOG'.
    2012-08-31 16:16:42.490 Server This instance of SQL Server last reported using a process ID of 2316 at 31/08/2012 4:16:35 PM (local) 31/08/2012 6:16:35 AM (UTC). This is an informational message only; no user action is required.
    2012-08-31 16:16:42.490 Server Registry startup parameters:     -d c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\master.mdf    -e c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\ERRORLOG    -l c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\mastlog.ldf
    2012-08-31 16:16:42.530 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
    2012-08-31 16:16:42.530 Server Detected 4 CPUs. This is an informational message; no user action is required.
    2012-08-31 16:16:42.600 Server Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
    2012-08-31 16:16:42.650 Server Node configuration: node 0: CPU mask: 0x0000000f:0 Active CPU mask: 0x0000000f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
    2012-08-31 16:16:42.680 spid7s Starting up database 'master'.
    2012-08-31 16:16:42.830 spid7s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
    2012-08-31 16:16:43.110 spid7s FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'SQLEXPRESS'.
    2012-08-31 16:16:43.140 spid7s SQL Trace ID 1 was started by login "sa".
    2012-08-31 16:16:43.150 spid7s Starting up database 'mssqlsystemresource'.
    2012-08-31 16:16:43.190 spid7s The resource database build version is 10.50.1617. This is an informational message only. No user action is required.
    2012-08-31 16:16:43.370 spid7s Server name is 'BANDMJ-PC\SQLEXPRESS'. This is an informational message only. No user action is required.
    2012-08-31 16:16:43.380 spid10s Starting up database 'model'.
    2012-08-31 16:16:43.380 spid7s Informational: No full-text supported languages found.
    2012-08-31 16:16:43.380 spid7s Starting up database 'msdb'.
    2012-08-31 16:16:43.510 Server A self-generated certificate was successfully loaded for encryption.
    2012-08-31 16:16:43.510 Server Server is listening on [ 'any' <ipv6> 49160].
    2012-08-31 16:16:43.510 Server Server is listening on [ 'any' <ipv4> 49160].
    2012-08-31 16:16:43.520 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLEXPRESS ].
    2012-08-31 16:16:43.520 Server Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].
    2012-08-31 16:16:43.520 Server Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection, restart SQL Server using the trace flag 7806. This is an informational message only. No user action is required.
    2012-08-31 16:16:43.520 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
    2012-08-31 16:16:43.520 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
    2012-08-31 16:16:43.620 spid10s Clearing tempdb database.
    2012-08-31 16:16:44.070 spid10s Starting up database 'tempdb'.
    2012-08-31 16:16:44.150 spid13s The Service Broker protocol transport is disabled or not configured.
    2012-08-31 16:16:44.150 spid13s The Database Mirroring protocol transport is disabled or not configured.
    2012-08-31 16:16:44.190 spid13s Service Broker manager has started.
    2012-08-31 16:16:44.220 spid7s Recovery is complete. This is an informational message only. No user action is required.
    2012-08-31 16:17:24.610 Logon Error: 18456, Severity: 14, State: 58.
    2012-08-31 16:17:24.610 Logon Login failed for user 'Guest'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: 10.0.0.40]
    2012-08-31 16:18:21.860 spid51 Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
    2012-08-31 16:18:21.870 spid51 Using 'xpstar.dll' version '2009.100.1600' to execute extended stored procedure 'xp_enumerrorlogs'. This is an informational message only; no user action is required. 

    Brad


    BHend

    Friday, August 31, 2012 7:00 AM
  • Hello Olaf,

    In the application running on the xp laptop I need to select an mdf file off the main laptop.  How would you do this if not by using the VB OpenFile Dialog?

    Brad


    BHend

    Friday, August 31, 2012 7:06 AM
  • Hello ,

    If your both computer are on a workgroup, you cannot use Windows authentification , but only SQL Server authentification. You have to provide a valid SQL Server login and its password ( not empty )

    See http://www.connectionstrings.com/sql-server-2008

    Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;

    USER ID is the key word for the SQL Server login

    Password : to define the password associated with the SQL Server login

    Trusted_Connection False ==> SQL Server authentification   True ===> Windows authentification

    Be sure that the SQL Server login is allowed to connect + is db_datareader + is db_datawriter for the database

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Monday, September 3, 2012 11:03 PM
  • Hello Papy,

    Thank you for that.  Can you explain the User ID.  Do I need to include the "slave" computers name in the User ID.  For example the computer hosting the connection is called "BandMJ", while the computer accessing the database is called "Brad-Laptop", if I added a new login called "AucSlave" would this name be sufficient or would I need the computer name included i.e. User ID = BRAD-LAPTOP\AucSalve or User ID = BandMJ\AucSlave or would User Id = AucSlave work.  Becuase I want both the hosting computer and the Slave computer to access the same file and be aware of each others changes would I just need to ensure User Instance = False? 

    Thanks again for your help

    Brad


    BHend

    Monday, September 3, 2012 11:37 PM
  • User ID = BRAD-LAPTOP\AucSalve or User ID = BandMJ\AucSlave or would User Id = AucSlave work.  Becuase I want both the hosting computer and the Slave computer to access the same file

    Hello Brad,

    The last one, without any host or workgroup name. For a SQL login you really only use the login name. The convention hostname\... is only for windows accounts.

    The both machine (SQl Server) can't access the same database file, one db file can be only accessed by one SQL Server at the same time. You have to attach the datbase file as a database on the SQL Server on machine "master" and from the "slave" machine you have to logon to the SQL Server on "master" to access the data. And for this you have to create a SQL login + grant access permission on "master" SQL Server.


    Olaf Helper
    Blog Xing

    Tuesday, September 4, 2012 6:10 AM
  • Hello,

    In your 4th post , i hae read 2 important lines in your error log file :

    "08/31/2012 16:17:24,Logon,Unknown,Login failed for user 'Guest'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: 10.0.0.40]"

    "2012-08-31 16:16:42.490 Server Authentication mode is WINDOWS-ONLY."

    If you want to use the SQL Server authentification mode, you must enable it ( and also you must enable the remote connections which is disabled with a defalt install )

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Tuesday, September 4, 2012 7:31 AM
  • Hello Olaf,

    I sense we are getting closer.  I now try to connect sith this connction string:

    "Data Source=BandMJ\SQLEXPRESS;Initial Catalog = MyDatabase.mdf;User ID = "AucSlave"; Password="Password"

    I get the following errors:

    On the Slave Computer I get:  "Cannot open database "MyDatabase.mdf" requested by the login.  The login failed. Login failed for user "AucSlave""

    On the SQL Server Management Studio log file I get:

    Login failed for user "AucSlave".  Reason: Failed to open the explicitly specified database [CLIENT: 169.254.173.121] Error: 18456 Severity: 14 State: 38

    The SQLServer has the following settings:

    Server Authentication mode is set to SQL Server and Windows Authentication Mode

    MyDatabase is attached with AucSlave having the following permissions:

     Connect    =   Grant

     Update     = Grant

    Security -> Logins -> AucSlave -> General -> default database is MyDatabase

                                                  -> Server Role has Public and sysadmin ticked.

                                                  -> User Mapping has MyDatabase with db_owner and public ticked.

    It seems I have something not quite right as the ssms clearly recognises the slave trying to connect to the mdf file but not getting there.

    Have you any ideas?

    Thanks again

    Brad


    BHend

    Thursday, September 6, 2012 5:35 AM
  • Hi

    Please use the following Connection string

    Data Source=BandMJ\SQLEXPRESS,1433;Initial Catalog = MyDatabase.mdf;Integrated Security=False;User ID = "AucSlave"; Password="Password"

    Here 1433 is default TCP Port no for SQL Server

    Still not getting, Please check the following

    Open SQL server Management Studio ,
    1) Object Explorer -> Expand "Security" -> Right Click to create new User at SQL server and set all privileges for this user. (Right click on new user, select properties and we can get many options)

    2)Right click on Server Instance->
                             A) Properties ->Security -> Select "SQL server and Windows Authentication Mode"
                             B) Properties ->Connections ->Select "Allow Remote connections to this server"
                             C) Properties ->Permissions-> Select created user and Grant Permissions
    3)Start -> All Programs -> Sql Server 2008 R2 ->Configuration Tools -> SQL Server Configuration Manager

                             A)Expand each option on left side, and on clicking protocols , we can see Shared Memory, Named Pipes, TCP/IP and Via. Enable first 3 by right clicking on that
                             B)On Selecting SQL SERVER 2008 SERVICES on left panel, we can see "SQL SERVER" and "SQL SERVER BROWSER". Right click on each one and select properties. and do the following
                                    i) select tab "SERVICE", set "Start Mode" as automatic, Click Apply                                                              

      ii) select tab "LOG ON",  select "Built-inAccount:" , then select "Network Service" from Combo box, click apply. System will ask whether to restart service and restart.
      iii) We can restart the service by, Right click on SQL SERVER" and "SQL SERVER BROWSER and click "Restart"
      iv) Above steps has to be done for both SQL SERVER" and "SQL SERVER BROWSER

      v)select your instance from  'SQL Server Network Configuration'-----,  open properties of TCP/IP -----, In 'IP Adresses' Tab we can see "IP ALL' .  Set TCP Port value to 49477

    • Restart SQL Server and try

    4)Control panel-> Windows firewall->Advanced Setting->select Inbound Rules-> New Rule
        Create new rule for program "sqlbrowser.exe" (find the location of this files at SQL SERVER installalation folder)
        Create new rule for program "sqlserver.exe"
        Create new rule for port 1433

    Thanks

    Johnson

    • Marked as answer by BHend Sunday, October 28, 2012 10:54 PM
    Sunday, October 28, 2012 4:52 AM
  • Thanks Johnson & everyone who contributed.  By a combination of all this advise as well as changing the security the folder containing the database file we now have "lift off."

    Thanks again, this forum is an excellent resource thanks to the people who freely offer their knowledge and experience.  It IS appreciated..


    BHend

    Sunday, October 28, 2012 10:56 PM