I consider the unit sphere with parametrization $\sigma: U \rightarrow \mathbb{R}^3$ given as
$$\sigma(u,v) = (\cos(u)\cos(v), \cos(u)\sin(v), \sin(u))$$
Now consider $w = (1,1,-1)$ which belongs to the tangent space at $p = (\pi/4,0)$, that is $w \in T_p \sigma(U)$.
My question is: How do I find a curve $\gamma: I \rightarrow \mathbb{R}^3$ on $\sigma(U)$ such that $\gamma(t_0) = \sigma(p)$ for som $t_0 \in I$ and such that $w$ is a tangent vector at this point, i.e. $\gamma'(t_0) = w$?
I tried several things, among this I tried to consider the equation for the tangent space and unit sphere and tried to derive some relations which could lead me to a candidate curve but was unsuccesful.
I'm guessing this is just something you were working on for recreation. The problem is that $w$'s coordinates are computed in the standard frame $(1,0,0), (0,1,0)$ and $(0,0,1)$ whereas vectors in $T_p \sigma(U)$ are computed in terms of,
$$\{ e^1:=\sigma_u(p), e^2:=\sigma_v(p),e^3:= \sigma_u(p) \times \sigma_v(p)\}$$
Hence, if you want $\gamma \in \sigma(U)$ i.e not just tangent at $\sigma(p)$ then $\gamma$ is on the sphere for a neighborhood of $\sigma(p)$ and so,
$$w=\gamma'(t_0) = \sum_{j=1}^2 \bigg(\gamma'(t_0) \cdot e^j(p)\bigg) \ e^j(p)$$
If the above equation can we solved then there must exists an $\alpha$ such that $(\sigma \circ \alpha)'(p) = w$ . Thus you can take $\gamma(t) = (\sigma \circ \alpha)(t)$. If you allow that $\gamma$ just be tangent at $\sigma(p)$ then take $\gamma(t) = \sigma(p) + tw$. If you want an explicit formula for the curve then take,
$$\gamma(t) = \frac{\sigma(p) + wt}{\|\sigma(p) + wt\|}$$
In terms of how to compute $\alpha$, observe that $w$ in terms of $T_{\sigma(p)} S^2$ is a two dimensional vector $(a,b) = (\gamma'(t_0) \cdot e^1(p), \gamma'(t_0) e^2(p))$ and so to move in the direction $w$ from $\sigma(p)$ means to travel along the image of the line $p+t(a,b)$ and this is $\alpha(t)$, why? Well by chain rule,
$$(\sigma \circ \alpha)'(0) = Df(p) \cdot \alpha'(0) = \begin{pmatrix} \sigma_u(p) & \sigma_v(p) \end{pmatrix} \begin{pmatrix} a \\ b \end{pmatrix} = w$$
Hence, you can also take $\gamma(t) = (\sigma \circ \alpha)(t)$.