Category Archives: SQL

Simple ways to test SQL connectivity

While installing Configuration Manager 2012 R2, you may have a problem connecting to the remote SQL server.  Here are some ways you can use to test remote SQL connectivity from your Primary SCCM server:

  1. Telnet to the SQL server’s listening port on 1433.  This approach will require a telnet client.  To install the native telnet client on Windows 7/8.1, run the command "OptionalFeatures.exe" and then place a checkmark next to “Telnet Client” and click on OK.
    OptionalFeatures.exe
    To use telnet.exe to connect to the SQL server’s port 1433, open a command prompt and type "telnet.exe <sqlserverhostname_or_ip> 1433".  If connection fails, troubleshoot your SQL server accordingly.  A successful connection will appear as a blank screen:
    telnet1
  2. Create an ODBC System DSN to see if connection is successful.  To add a “System DSN” open the ODBC Data Source Administrator by running the following command "C:\Windows\System32\odbcad32.exe" (64-bit) or "C:\Windows\SysWOW64\odbcad32.exe" (32-bit).  Then click on the “System DSN” tab and click on “Add…”.
    odbc1
    Select “SQL Server” and click on Finish.
    odbc2
    In the “Create a New Data Source to SQL Server” window, name the data source anything you want as we won’t be saving this connection – it’s just a connectivity test.  In the “Server” field enter your SQL server’s hostname or IP.
    odbc3
    Leave the next screen on defaults and continue.
    odbc4
    Again, leave the next screen on defaults and continue.
    odbc5
    The next screen you can also leave on defaults and click on Finish.
    odbc6
    In the configuration summary window, click on “Test Data Source…”.
    odbc7
    If test was successfull, you will see the below message.
    odbc8
  3. Last but not least and probably the less intrusive way of testing SQL connectivity is by use of Universal Data Link files (.UDL).  This method does not require the installation of a telnet client or has as many steps as the “System DSN” method and support for this method is built into every windows version.  You can delete the file afterwards to “clean-up” the system.  Right-click anywhere on your desktop and select “New”, then “Text Document”.
    udl1
    Name the text document anything but make sure to change the “.TXT” to “.UDL”.  Then double-click on the resulting file.
    udl2udl3
    Double-click on the .UDL file and type the hostname or IP of your SQL server, then select a user account with permission to the SQL server and select a database.  Then click on “Test Connection”.
    udl4
    If connectivity test was successful, you will receive the below message:
    udl5

This isn’t an exhaustive guide to testing SQL connectivity.  Your mileage may vary depending on what the root cause of your connectivity issue, but these steps will certaintly start you off in the right direction.

SQL Query: Find user’s primary device by deparment AD attribute.

We are getting ready to rollout a Windows 7 desktop deployment in order to “clean up” the software license reports.  Part of this project entails creating static collections for the various departments within the company which will have an OSD task sequence deployed to.  In order to take advantage of User Device Affinity in SCCM 2012, I requested an SAP report of all users within the affected departments.  The report I received contained fields within Active Directory such as the “Department” attribute.  I then created the following SQL query to retrieve all users within a deparment and display their primary device.

Use [CM_BSG]
select upm.UserResourceID, upm.MachineID, vru.Name0 as [User Name],
vrs.Name0 as [Machine Name], vru.department0 as [Department]
from v_UsersPrimaryMachines  upm left join v_R_User AS vru
on upm.UserResourceID = vru.ResourceID left join v_R_System vrs
on upm.MachineID = vrs.ResourceID where vru.department0 like '[VALUE OF AD DEPARTMENT FIELD HERE]'

If you are looking for User Device Affinity for a specific username, you can easily modify the query as the following:

Use [CM_BSG]
select upm.UserResourceID, upm.MachineID, vru.Name0 as [User Name],
vrs.Name0 as [Machine Name], vru.department0 as [Department]
from v_UsersPrimaryMachines  upm left join v_R_User AS vru
on upm.UserResourceID = vru.ResourceID left join v_R_System vrs
on upm.MachineID = vrs.ResourceID where vru.name0 like '%[USERNAME HERE]%'

Or all devices for all users in the database:

Use [CM_BSG]
select upm.UserResourceID, upm.MachineID, vru.Name0 as [User Name], 
vrs.Name0 as [Machine Name], vru.department0 as [Department] 
from v_UsersPrimaryMachines upm left join v_R_User AS vru 
on upm.UserResourceID = vru.ResourceID left join v_R_System vrs 
on upm.MachineID = vrs.ResourceID where vru.name0 IS NOT NULL

SQL query to obtain list of SCCM SQL views for report building.

I found this post I thought would be great to share from my fellow colleague’s blog, Eswar Koneti.  It details the SQL query needed in order to obtain a list of SQL views included in the SCCM 2012 database for report building purposes:

Select distinct table_name,COLUMN_NAME From Information_Schema.columns where table_name LIKE ('v_%') Order by table_name

UPDATE:  Here is his post for the SCCM 2012 R2 SQL Views!  A direct link to the spreadsheet is also included in my links sidebar.