How to connect your web script with MS SQL Server? (version not important)

I must be the only noob who doesn't know how to use it but I spent a working day just to get the connection setting right. I get paid for that, so I must return some knowledge to the society. I installed SQL Server 2000 happily, thinking that it will be as intuitive as any other software that I have seen, but there are still some traps here and there.

During installation, I was asked questions that I did not know understand and I did not have the installation manual with me to teach me what each option meant, so I just clicked whichever I thought sounded more correct. If you ask me now what I chose for the various options, I will not be able to tell you because I really didn't know a single thing. The wizard then popped out to guide me through more installations which I did not know what it was for until I deleted something and then it was "Eureka! So that was what the wizard did!"

All other settings are quite standard but the problematic setting is the mixed mode authentication which really doesn't make any sense to a noob. There are two kinds of authentication methods, or rather methods for you to connect to the sql server. The first method, which doesn't work if you are using 3rd party scripts/programs to call it, is the windows authentication. It is a pop up window that requires you to type in your username and password. Up till now, I still don't understand how it works, but the username and password should be the one used to login the machine that contains the harddisk that stores the sql database. It's confusing right? For example, you are a client PC and your sql database is on a network. You also need to install MS SQL Server on your client PC if you want to read/write/execute queries on the database. The windows authentication in this case will pop up a window or something and ask you to select the database, mynetwork/db1, for example. After that, you have to enter the username and password to access that database, which is also called the database server.

Ah... so long-winded. The mixed mode is the one that is most commonly used. It allows windows authentication and sql server login. The additional login here is the sql server login which actually lets you connect to the sql server by scripts. The username and password used will be the same as in the windows authentication method. In the script, your connection statement will be something like this:

DATABASE=databasename;SERVER=servername;UID=username;Password=password

However, noobs like me don't know how to fill those items in. When I opened the sql software, I looked at the different folders, and added a database in, thinking that it will work but it didn't. *haha* By defualt, the database name is "master". I don't know how to do other fanciful stuff, but the very least, anybody should know that the default database is "master" because this will save you the time hunting every corner for your table. The username and password is also very tricky because throughout the installation process, I did not recall filling in information to create a user or anything to that extent.

The default username is "sa" and the password can be set to anything you fancy, but no noob will know where to find it. I searched microsoft.com for quite some time before I saw the light. Every database has its set of permitted users, so double click on the database, go to "Users", then you see the users. If you don't see any user or the "Login Name" field is empty, or you see the login name but don't know the password, or whatever, go to Security/Logins to create a new login. You will not be able to add a user into Databases/dbname/Users if the user login hasn't been created. Right click, and you should see a New Login option. Everything else is self explanatory but just remember to select "SQL Server Authentication".

No ODBC setup, that I initially thought would be needed, was required when I was meddling with the settings.

Noob for today, not a noob tomorrow.

Global.asa and Global.asax

The global file is a file that is placed in the root directory that is always executed before loading any page from this root directory onwards. Global.asa actually runs like any asp script, just that it has standard methods to trigger the execution like session/application start/end. Global.asax is a bit different. There isn't standard session/application start/end methods, and it doesn't run like an asp file. Instead, it runs like an asp.net file. Since sharepoint is using asp.net, you have to put a global.asax file instead of a global.asa file if you plan to use it. Before any .aspx file is loaded from your site, global.asax will be executed.

An example of the global.asax file I created is shown here for noobs like me to get a feel. Seriously, you don't need to know C# to write scripts. You can also use visual basic, which you also don't really need to know. Everything is like english. As long as you have the dictionary, with some grammar rules, you can compose sentences. You can also search for scripts and copy, like how you wrote your first english essay.

[%@ Application ClassName="Globals" language="c#" %]
[%@ Import Namespace="System.Data" %]
[%@ Import Namespace="System.Data.SqlClient" %]
[%@ Import Namespace="System.Web" %]

[SCRIPT language="C#" runat="server"]
protected void Application_MyMethod(Object sender, EventArgs e)
{
...
}

A Web Part or Web Form Control on this Web Part Page cannot be displayed or imported because it is not registered on this site as safe.

Situation: You have just finished compiling your web part in VS.NET and can't wait to see it appear on your site, but after you import your web part, you can't upload it because it's not safe.

Cause: 1. Security settings incorrect. 2. DLL file not found.

Solution: 1. Open c:\inetpub\wwwroot\mysharepointdir\web.config. Make sure you have a safe control entry which looks something like this:

[SafeControl Assembly="LibOnline, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9929f92c6f109109" Namespace="LibOnline" TypeName="*" Safe="True"/]

Scroll further down, change the trust level to Full. By default, it's WSS_Minimal, like this:

[trust level="Full" originUrl="" /]

2. Copy and paste the DLL file for your webpart from the default project\bin\Debug directory into c:\windows\assembly OR set the build output path for the project to c:\inetpub\wwwroot\mysharepointdir\bin.

If the webpart used to work but suddenly doesn't work, it could be due to the static IP address on the server. As such, go to IIS Manager, right click your site, click properties, under the Web Site tab, select "(All Unassigned)" for IP address. When accessing the site, use http://servername/sitename.

The type or namespace name 'SharePoint' does not exist in the class or namespace 'Microsoft'.

Situation: You try to write a script outside sharepoint and you get a compilation error when you try to import a microsoft library.

Log from parser:

Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS0234: The type or namespace name 'SharePoint' does not exist in the class or namespace 'Microsoft' (are you missing an assembly reference?)

Source Error:

Line 3: [%@ Import Namespace="System.Data.SqlClient" %]
Line 4: [%@ Import Namespace="System.Web" %]
Line 5: [%@ Import Namespace="Microsoft.SharePoint" %]
Line 6: [%@ Import Namespace="Microsoft.SharePoint.WebControls" %]

Cause: Programs are dumb, they don't know where to look for DLL files.

Solution: Create a bin folder in your sharepoint directory, for example c:\inetpub\wwwroot\bin or c:\inetpub\wwwroot\mysharepointdir\bin, then go to c:\windows\assembly, copy Microsoft.Sharepoint.dll, pasted in the bin folder. In your script, add the line: [%@ Assembly Name="Microsoft.Sharepoint" %] in between line 4 and 5.

Change square brackets to triangle brackets.

The following file(s) have been blocked by the administrator.

Situation: You try to load a .asp file or some other file type other than .aspx and sharepoint refuses to let you view it. Whether or not the server can interpret asp code is a different matter altogether.

Cause: By default, there is a long list of file extension types that are blocked by sharepoint.

Solution: Go to Sharepoint Central Administration, log in as administrator, scroll down to Security Configuration, click Manage blocked file types, delete the file types that you don't want to block and then click ok.

Where is the Global Assembly Cache (GAC)?

Situation: Being the noob like I am, you read about GAC so many times, but all the books or online help that you can get just keeps telling you to go to the GAC, but where is it?

Cause: I am a noob. Really, no one is at fault. I really should have been smart enough to know where it is without anybody telling me.

Solution. Go to c:\windows\assembly. That folder is called the GAC. To add a dll file, just drag and drop. To remove, just right click on the dll file and click uninstall.

Debugging is not supported under current trust level settings.

Situation: Your dearest portal refuses to load default.aspx and you wonder what tantrum she is throwing. You thought it's the settings in Tools -> Internet Options -> Security, but no matter what you change, it still doesn't work.

Cause: You have added in a new application (e.g. an ASP.NET object going with the .dll tag) to a subsite. This is so subtle that you didn't think that it will be going to affect your other buddies.

Solution: Go to the web.config file in the root directory and change the cranky woman's trust level: [trust level="Full" originurl=""] under the [system.web] tag. The default should be "WSS_Minimal" which basically means she doesn't trust you enough to bring your friends home.