SQL Server 2008 Express Edition : Connect remotely from another server using SQL Management Studio

So, this time we're going to connect to SQL Server Express edition remotely from another server. As we know if we install SQL Server Express Edition it doesn't come up with SQL Management Studio Express on it, and that means we won't be able to manage the SQL Server Express Edition. In this case, we're gonna connect to SQL Server Express Edition by using SQL Management Studio in the separated server. All you need to is enable the remote connection from the SQL Server Configuration Manager of your Express Edition.

1st step---
Open SQL Server Configuration Manager and point to SQL Server Services.

2nd step---
Right click on SQL Server Browser > Properties
On the Service Status, click Start button, and then Apply then OK.

3rd step---
Notice that the SQL Server Browser is now running.

Now you can try to connect to the SQL Server Express from any different server using SQL Management Studio.

Thanks to Linglom for sharing this.

Read more »

SQL Server 2008 R2: Connect to Different Domain and Server Name

Have struggled with this problem almost all day. I couldn't connect to another domain server using SQL Management Studio 2008 R2. Precisely, my other domain server is using Hyper-V and i called the server name as DOMAIN1. In this case i couldn't get access easily by using the server name but what i need is connect using the IP Address of DOMAIN1. In this case i defined the IP address of DOMAIN1 as 192.168.0.11

1st step---
Make sure you can connect to DOMAIN1, add another IP Address. In this case, i am using IP 192.168.0.* so i can connect. For example i defined the IP address of my SQL Server computer as 192.168.0.9

2nd step---
Check the DOMAIN1 firewall, make sure it doesn't block connection from your computer. To set up Firewall, go to Windows Firewall with Advance Security.

Right Click on Windows Firewall with Advance Security and point to Properties > Domain Tab > and Allow inBound connection.

3rd step---
Run Command Prompt:

cd\ <enter>

runas /user:DOMAIN1\admin /netonly "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe" <enter>

Enter the password for DOMAIN1\admin then <enter>

wait until SQL Management Studio 2008 R2 open by itself.

4th step---
Once you get the Connect to Server Window. Fill in the Server Name as shown below:
Server type: Database Engine
Server name: 192.168.0.11\InstanceName
Authentication: Windows Authentication
Then click connect button.

Hope it works. Mine is working very well, now i can connect to my Hyper-V SQL Server. :) Good luck!

Read more »

SharePoint 2010: Specify the Default Database Server

If you have look at my posts before this, this post is connected to Migrating SharePoint 2010 Databases from SQL Server 2008 Express Edition to SQL Server 2008 R2 Enterprise Edition. After detach and attach all MOSS 2010 databases from SQL Server Express, now we have  to go back to SharePoint Central Administration. 

1st step---

Log on to your Central Administration as farm administrator.

2nd step---

In the SharePoint 2010 Central Administration Page go to Application Management > Databases > Specify the default database server.

3rd step---

In the default database server page, fill in the Domain\SHAREPOINT_DB_Instance of the SQL Server Instance that you'd like to be as the default.

And if on that Instance you are using SQL Authentication, then you have to fill in the Username and Password, instead leave it blank if you're using Windows Authentication.

Sometimes you may get an error message if there's an issue with the permission for NT AUTHORITY/NETWORK SERVICE group like shown below:

Login failed for user 'NT AUTHORITY\NETWORK SERVICE'

This happens when you're using Windows Authentication, and means you will need to grant permission for NT AUTHORITY/NETWORK SERVICE. Below are some steps that i did to grant permission for NT AUTHORITY/NETWORK SERVICE group.

1st step---

Open SQL Management Studio and then connect to the Database Instance. After you are connected, right click on the Database Instance and point to New Query, because we are going to use Transact-SQL to grant the permission.

2nd step---

Execute the script below:

sp_grantlogin 'NT AUTHORITY\NETWORK SERVICE'

After that you will need to go back to the SharePoint Central Administration, and redo the 2nd step---  and 3rd step--- up above. You will not find any error anymore. :)

Read more »

SharePoint 2010 : Detach SharePoint databases from SQL Server Express Edition & Attach to SQL Server Enterprise Edition

As default on the new installation, Microsoft Office SharePoint Server (MOSS) 2010 using SQL Server Express Edition to store the databases. To migrate MOSS 2010 databases to SQL Server Enterprise edition, you need to install SQL Server Management Studio first so you can manage all Database Engine easily. Before you do databases detach, please read this article carefully from Microsoft official site. According to the information and requirements provided by Microsoft you will need to stop some of the SharePoint services. You can manage by using Administrative Tool > Services, but i'm using a very quick tool from CodePlex called SharePoint 2010 Service Manager. You can download from here for FREE!

So below are the steps to detach your SharePoint databases from SQL Server Express Edition and attach them again  to SQL Server Enterprise Edition.

1st step---

Go to SQL Server Management Studio, connect to your SharePoint Server database.

This is what i did:

Server type  : Database Engine

Server name : Domain\SHAREPOINT

Then click Connect.

2nd step---

In the Object Explorer, point to Connect button and choose Database Engine. Then connect again to your SQL Server Enterprise Edition server.

3rd step---

After installing SharePoint 2010 Service Manager, click on the Stop SharePoint 2010 button to stop all the services we need to detach the MOSS 2010 databases.

After we stop all the services, note that the SQL Server also stopped. We need to start it again manually from SQL Server Management Studio.

4th step---

Point to the database that we need to detach. Right click, then point to Tasks > Detach..

5th step---

Go to Windows Explorer and find the location of your SQL Server Express databases, then copy the .mdf and .ldf file to the SQL Server Enterprise databases location.

6th step---

Back to SQL Server Management Studio, but now we focus on the SQL Server Enterprise Edition server. Expand the Databases. Then right click on Databases and choose Attach..

The new dialog for attach database will show up after that. Click on Add. And it will show the dialog to locate the .mdf file.

7th step---

Click OK, and then OK again to attach the database file. You will notice a new database has been attached to your SQL Server Enterprise Edition. If it doesn't appear then right click on the server name and Refresh.

Good Luck.

Read more »

SQL Server 2008 Express Edition Error: Cannot show requested dialog. ADDITIONAL INFORMATION: Parameter name: nColIndex Actual value was -1. (Microsoft.SqlServer.GridControl)

This morning i have found the way to detach SharePoint databases from SQL Server 2008 Express Edition and then attach all databases to our new SQL Server 2008 R2 Enterprise edition. But then i found problem on re-attach the databases to SQL Server Express edition. It returns me an error as shown on the image below.

 

TITLE: Microsoft SQL Server Management Studio

------------------------------

Cannot show requested dialog.

------------------------------
ADDITIONAL INFORMATION:


Parameter name: nColIndex
Actual value was -1. (Microsoft.SqlServer.GridControl)

------------------------------
BUTTONS:

OK
------------------------------

Then after hours looking for the solution, finally i found the article by googling it, saying that this is one of the SQL Management Studio bugs. So all you need to do is using Transact-SQL to attach the database.

Below is the T-SQL script that i used to re-attach my SharePoint database and worked very well.

EXEC sp_attach_db @dbname = N'Your_SharePoint_DBName',
    @filename1 = N'[drive]:\DB_Attach_Location\SharePointDB_File.mdf',
    @filename2 = N'[drive]:\DB_Attach_Location\SharePointDB_Log.LDF';


Example:

EXEC sp_attach_db @dbname = N'SharePoint_AdminContent_cc5d5fea-c432-4746-930e-cfc2138cffd3',
    @filename1 = N'E:\Data\MSSQL10.SHAREPOINT\MSSQL\DATA\SharePoint_AdminContent_cc5d5fea-c432-4746-930e-cfc2138cffd3.mdf',
    @filename2 = N'E:\Data\MSSQL10.SHAREPOINT\MSSQL\DATA\SharePoint_AdminContent_cc5d5fea-c432-4746-930e-cfc2138cffd3_log.LDF';

Once you run the query you may notice the new database has been attached successfully. Hope it helps.

Read more »

SQL Query: Adding Active Directory Users Attributes To An Existing Table Or Specified Column

OK, here it is, i was trying to create a timetable SharePoint webpart for our school, and using SQL Server Database. And i want the timetable appears as the domain user logged on to SharePoint. So my first step to create this webpart was creating the database and tables. From the previous topic i have explained how to get the Active directory users and attributes, and so i improved the sql query to copy all my AD retrieval results into my 'teacher' table.

Below is the query that i ran:

use TimeTable
go
INSERT INTO teacher(initials,logOnID,preferredName)
SELECT initials,samAccountName, displayName FROM OPENQUERY(ADSI,'SELECT initials, samAccountName, displayName, mail FROM ''LDAP://OU=Teaching Faculty,OU=Staff,DC=BISDRAGONS,DC=COM'' WHERE objectClass=''Person'' AND objectClass = ''User'' ORDER BY samAccountName') where mail IS NOT NULL

And it worked very well for me. :)

Read more »

SQL Query: Retrieve Active Directory Users List

Today i was struggling getting the active directory users list from our domain, but finally it worked. Here's the query.

1st step---

Thanks to Alina from MSDN blog (http://blogs.msdn.com/b/alina/archive/2009/04/16/ldap-query-to-get-a-user-memberships-in-sql.aspx) for this awesome SQL script:

EXEC master.dbo.sp_addlinkedserver @server = N'ADSI',
@srvproduct=N'Active Directory Services', @provider=N'ADsDSOObject',
@datasrc=N'bisdragons.com'
GO

Note: Change @datasrc=N'your_domain.com' 

2nd step--- 

Run this query:

SELECT samAccountName, givenName [First Name], sn [Last Name],mail FROM OPENQUERY(ADSI,'SELECT samAccountName,givenName, sn, mail FROM ''LDAP://OU=Teaching Faculty,OU=Staff,DC=BISDRAGONS,DC=COM'' WHERE objectClass=''Person'' AND objectClass = ''User'' ORDER BY samAccountName') WHERE mail IS NOT NULL

Note: change "LDAP://OU=your_organizational_unit, DC=your_domain,DC=COM(*optional com,org,ca,etc...)" 

You can also modify the SQL query above to get another attributes from your Active Directory. This link provides a complete Active Directory attributes in pdf.

Good luck!

Read more »