New Report Microsoft SQL Connection
How to connect to MS SQL Server in the "New Report from Database" wizard, on the Connection tab.
I have found that the quickest way for me is to keep a line like the following in a notes text file I can quickly access, and I cut-n-paste it straight into the Connection field - this uses Windows Authentication so that no username and password needs to be specified :
server=OURSERVER; database=TESTDB; Integrated Security=SSPI
You can leave blank all the controls in the "SQL Server" frame below this.
The connection details can be seen later in Data..Data Sources from the top menubar, and will appear in the RDL XML as
<DataSources>
<DataSource Name="DS1">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>server=OURSERVER; database=TESTDB; Integrated Security=SSPI</ConnectString>
If you'd rather let the wizard detect the server for you, try the following. With the "Connection Type" set to SQL a new group box (frame of controls) will appear. It lets you search for SQL 2005/2008 servers and select any that come back, in a combo box. You can then enter your username and password on the server and click "Search for Databases" and a list of all databases on the server will be returned.
You may receive a server authentication error. If it looks like:
You can fix this by setting your server authentication to "SQL Server and Windows Authentication mode".
